What ought to be a simple formula

[martingaleh]

I'm trying to do something like calculate(sum([amt]),<>a||dimension[doc]<>b)). It seems so straight forward, but then maybe I need a fake dimension table for the fact column I'm filtering on. Before I implement a dumb solution like that, I thought I'd ask to see if maybe my syntax is just wrong because I'm getting no filtering action on my pivot thing at all

 

[martingaleh]

You know, I'm not trying to replicate sql, sql doesn't let you step on predicates with other predicates, for example. I'm just trying to understand what's going on.

So yes these two queries

Select Sum(amt) From (
     Select fact.key, amt From fact Inner Join onedim on (fact.onekey = onedim.onekey)
    Union
    Select fact.key, amt From fact Inner Join twodim on (fact.twokey = twodim.twokey)
    ) tOr
Inner Join fast tmain On (tmain.key = tOr.fact.key)
Inner Join otherDim on (tmain.otherkey = otherDim.otherkey)
Group By otherDim.otherkey;

We can use Full Outer Join too

Select Sum(amt) From (
     Select key, amt From (
         (Select fact.key, amt From fact Inner Join onedim on (fact.onekey = onedim.onekey)) tone
         Full Outer Join
         (Select fact.key, amt From fact Inner Join twodim on (fact.twokey = twodim.twokey)) ttwo
         On (tone.key = ttwo.key)
    ) tOr
Inner Join fast tmain On (tmain.key = tOr.fact.key)
Inner Join otherDim on (tmain.otherkey = otherDim.otherkey)
Group By otherDim.otherkey;

Will both work because the union will automatically remove the duplicated rows between the two tables in the union where the two predicates intersect.

1. Does Union do the same thing in DAX, as the literature says it doesn't. If it doesn't, your resulting query will have duplicate rows
2. Union, like filter and all are the same return type, they must be because they're on the same position in the calculate function. This is where it's handy to know what the return type is.

Actually, I think this is the crux of the matter:

The issue here is filtering vs. iterating over a table.

I have to begin to understand this. There really shouldn't be a difference, and for basic sql there isn't. Later on, you learn that sql doesn't actually build out the entire table, it filters out the tables first and then chooses the joins based on cardinality. I'm sure this filter vs iterating thing is similar. Which chapter in the purple book covers iteration?

Finally, I'm fine with cross filter, it was my very first attempt on my first day with DAX. It's just it's my first attempt on my first day, so I figured if someone else has something better, I'm all ears.

Really your case has and other ways of solution in SQL

I used

Select Sum(amt) From (
     Select fact.key, amt From fact Inner Join onedim on (fact.onekey = onedim.onekey)
    Union
    Select fact.key, amt From fact Inner Join twodim on (fact.twokey = twodim.twokey)
    ) tOr
Inner Join fast tmain On (tmain.key = tOr.fact.key)
Inner Join otherDim on (tmain.otherkey = otherDim.otherkey)
Group By otherDim.otherkey;

We can use Full Outer Join too

Select Sum(amt) From (
     Select key, amt From (
         (Select fact.key, amt From fact Inner Join onedim on (fact.onekey = onedim.onekey)) tone
         Full Outer Join
         (Select fact.key, amt From fact Inner Join twodim on (fact.twokey = twodim.twokey)) ttwo
         On (tone.key = ttwo.key)
    ) tOr
Inner Join fast tmain On (tmain.key = tOr.fact.key)
Inner Join otherDim on (tmain.otherkey = otherDim.otherkey)
Group By otherDim.otherkey;

In Excel 2013 or 2010 you are able to use full outer DAX equivalent

= Calculate(Sum(fact[amt]), onedim[onekey] = 1; twodim[twokey] <> 2) 
+ Calculate(Sum(fact[amt]), onedim[onekey] = 1; twodim[twokey] = 2) 
+ Calculate(Sum(fact[amt]), onedim[onekey] <> 1; twodim[twokey] = 2)

Is it longer than SQL? Yes, it is. Is it difficult? I am not sure.
Has DAX weirdnesses? As for me an answer is yes.
Let see on my measure Union function work as SQL Union (but it is Union All by defintion). Ok, let rewrite it in other style.

=Var factKeyByOneDim = CALCULATETABLE(VALUES('fact'[key]); 'oneDim'[oneDim] = 1)
Var factKeyByTwoDim =CALCULATETABLE(VALUES('fact'[key]); 'twoDim'[twoDim] = 2)
Var factKeyByOr =  UNION(factKeyByTwoDim; factKeyByOneDim) 
Var factOrData = NATURALINNERJOIN('fact'; factKeyByOr)
Return SUMX(factOrData; 'fact'[value])

In this case DAX Union works as Union All. I do not know why. Maybe it is special behavior inside Calculate or CalculateTable.
I do not understand why in SUMX function in my example I must write 'fact'[value], but not factOrData[value].
I do not understand why it works

Calculate(Count(oneDim[onekey]); fact)[

But that does not at all.

Calculate(DictinctCount(oneDim[onekey]); Values(fact[onekey]))

In Microsoft Excel 2013 Building Data Model with PowerPivot authors of Definitive Guide wrote it so difficult for explanation
Regards,