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]

I tried so many other things, I have to say, after a whole day on this, when we get to a point where humans can tell what we want, but the computer can't, the failure is in the computer.
calculate(sum[amt],filter(newdim<>a||dim[doc]=b))
if(filter(newdim<>a||dim[doc]=b),sum(amt),0)

With and without the related key word, and with the dimensions already defined. Wow, this is a difficult language. The "secret" data types that are hidden but required in power pivot and explicit and required in other strong type languages like c++ and java are explicit for a reason. Not having them doesn't make it easier, it makes it harder because then I have to guess what type is coming out of an expression.

 

[martingaleh]

I finally figured it out. Holy god this is a hard language. The closest I got with the googling is: http://www.sqlbi.com/articles/filter-arguments-in-calculate/
but even then, they failed to mention that it works with different dimensions. Here is the syntax on How to filter a power pivot with an "or" predicate on two different dimension tables

calculate(sum[amt], //must be an aggregate function, not jsut a value like [amt]
filter(crossjoin(onedim,twodim),onedim[a]=b||twodim[c]=d))
)

by the way, the super simple sql equivalent is:
select sum(amt) from fact left join onedim on ?? left join twodim on ?? where ondim.a = b or twodim.c = d group by all your other measures

And then the engine is smart enough to figure out whether to use a nested, hash or merge join and you don't ahve to explicitly tell the computer to do a cross join or use filter, or use or not use related, another mystery key word, or maybe an if function with an aggregate or not aggregate wrapper around your measure. I'm sure there's a logic here, but it's not obvious and both Collie and Ferrari's books are super basic. I mean I'm trying to do "hard" things and I"ve been at this a day. How did other people get paid while they were learning this?

Finally, other people address this, but no one really does. The answer to whether you join dimension values onto the fact table in power query or power pivot is to use power query UNLESS you need to interact with that dimension and an aggregated value on the pivot table. If I was able to join dates and docs right into the fact table there would be no cross join craziness. Also, the computer wouldnt need to make a cartesian product to evaluate an "or" functions which query engine developers everywhere are laughing at.

 

[akice]

By your posts it seems that you don't have a fundamental understanding of how Power Pivot and Dax work. It seems you are trying to apply knowledge from other, procedural languages (c++ & java) to Dax which is a functional language. Dax is all about manipulating the filters that are placed on table columns which is accomplished using the various functions combined together. You need to study up on filter contexts, context transitions, filter propagation, and host of other things to understand how it all works.

And Interesting that you say it is a hard, difficult language when once you understand the basic concept of how it works it really isn't. There are nuances to it of course, but once you get the basics it isn't a hard language at all. But you can't skip over learning the basics. And I am not understanding what "secret" data types you are referring to(?)

Also, you should not need to perform the crossjoin which could of course quickly become expensive if the dimension tables are at all large. What you can do is something like the below; however it really depends on what you have on the rows/columns/filter area or slicers among other considerations.

measure :=VAR dim1 =
    MAX ( dimension1[columna] )
VAR dim2 =
    MAX ( dimension2[columna] )
RETURN
    CALCULATE (
        SUM ( facttable[columnAmount] ),
        FILTER (
            ALL ( facttable ),
            facttable[columna] = dim1
                || facttable[columnb] = dim2
        )
    )

is this code optimal for your situation? I have no idea. It depends. Power Pivot uses Vertipaq, an in-memory columnar database store compressed for optimization. For my example code efficiency is going to depend mainly on the cardinality of the comparison columns.

I hope all this helps. Put a little time into it and i'm sure you'll get it sorted before long.

 

[anvg]

akice, your solution has one problem OR statement is written a single fact table. But in this topic it is about using OR for values is defined in two dimension tables. I think it is better to use UNION for such OR filter

= CALCULATE(SUM('fact'[value]);    UNION(
        CALCULATETABLE('fact'; FILTER('oneDim'; 'oneDim'[oneDim] = 1));
        CALCULATETABLE('fact'; FILTER('twoDim'; 'twoDim'[twoDim] = 2))
    )
)

Example file https://1drv.ms/x/s!AhKJrasHVtohiS3smw6ImJ49ErmI
Regards,

 

[martingaleh]

akice, your solution has one problem OR statement is written a single fact table. But in this topic it is about using OR for values is defined in two dimension tables. I think it is better to use UNION for such OR filter

= CALCULATE(SUM('fact'[value]);    UNION(
        CALCULATETABLE('fact'; FILTER('oneDim'; 'oneDim'[oneDim] = 1));
        CALCULATETABLE('fact'; FILTER('twoDim'; 'twoDim'[twoDim] = 2))
    )
)

Example file https://1drv.ms/x/s!AhKJrasHVtohiS3smw6ImJ49ErmI
Regards,

I trust the Union() function here is just like the sql union, which is really a distinct union and not a union all which will repeat rows on that second union. I see what you're trying to do here, it's a Dax special. It's, I dont like the context I'm in, so I'm going to throw the whole thing out and build it back with my own table. Now do the unaddressed qualifiers survive the filter context in a calculate table? I dont know. I read page 109 of that purple book when they wrapped it into a filter. Here it's the opposite. And it will probably take me longer to figure out what's going on by testing permutations on a pivot table than to convince my organization to move to tableu, which is a free download for evaluation purposes.

You need to study up on filter contexts, context transitions, filter propagation, and host of other things to understand how it all works.

This is exactly my point! I would never have figured out the syntax above, and the syntax shouldn't have to reflect on how the engine works on the inside. If a human can figure out what I want to do, they can implement the behind the scenes logic to do stuff like this. Yes, I can go learn the row context and filter context logic of power pivots, but why should I have to? Even if I knew it, I don't get to see a query plan like I
can with SQL to tune it. I don't get to build out my own match and filter scheme like I can with query hints and indexes. I'm wasting a day and a half of my life learning concepts that are more complex than the lexical scoping of the javascript scope chain with none of the convenience of simple object extension.

Did you even read the Definitive Guide to DAX?

"The way you describe an algorithm in DAX is very different from SQL, Microsoft Excel, MDX or any other programming language

was written like its a badge of honor, not an imprecation for hubris. The ENTIRE fifth chapter was "you might think this would happen, but your're wrong" or "Surprisingly..." It happens so often, they got 3 stars on an Amazon review for it. Cognos, OBIEE, Tableau have all solved the problem of calculating inside a pivot table using concepts that are GENERALLY familiar. It's only Microsoft that would find a convoluted way to do something simple. This isn't the first time Microsoft used their monopoly power to stuff an industry non compliant solution down our throats. They did it in IE6 and now everyone uses Chrome. There's a reason why there are currently 600 people reading the excel forum and 5 people reading this one. We can vote for all the things that power pivot can do, but when there are 100 times more people voting for prettier graphs and cooler chart actions, you can bet your desires will never be met.

And I am not understanding what "secret" data types you are referring to(?)

In any other strongly typed language the documentation will look something like this

datatype function(datatype parameter, [datatype parameter ...)
So that I know exactly what data type I have to deliver and what data type I'm getting back. Even PHP, which duck types out integers and strings will give you this. When I first looked at the calculate function I had no clue what data type calculation was going to send back. It turns out it sends back something that goes right on the pivot table, i.e. it's the same type as returned by sum(), but not by just the measure [] which the collie book calls implicit and tells us not to use.
calculate(expression<expression>,filter<filter>,filter... <filter>)
https://msdn.microsoft.com/en-us/library/ee634825.aspx

I have no clue what an expression is, because it could be anything. My favorite was bullet point 3
The expression cannot use any function that scans a table or returns a table, including aggregation functions.
You mean like sum? because the common colloquial use for the words "aggregate function", of which aggregation function is awfully close and not otherwise defined in an easy to access place, would include functions like count or sum. You can see it in use in MICROSOFT'S own T-SQL documentation.

Also <filter>What's a <filter>Oh, like Calculatedtable. Is that a filter? Because that's the function I'm presented with in the solution below (or above).

I see now why Google won't let their analysts use power pivot and power query. They Microsoft building is right next to Corp Eng People in TC4. There would be homicides if they had to use power pivot. I'm not angry at the people on this forum. I could kick the dog instead over this matter, but he wouldn't understand why and this is the only place where us few power pivot victims reside. Given the difficulty of using this thing, it will remain few for the foreseeable future.</filter></filter></filter></filter></expression>

 

[akice]

I read your response - and to be honest I don't understand it. I mean I understand you are frustrated with Dax and all that, but to me you went out to the garage, grabbed a pneumatic nailer, and are now really frustrated it is lousy at pounding nails because you don’t know how to operate it. And you're mad at the nailer for that. Dax is a different tool developed to solve problems differently. I think we can both agree no one tool is the best for all situations. I agree that whatever data modelling you can do in SQL ahead of Dax, the better off you are. But again, SQL can't solve problems like Dax can. Of course the opposite is true. You shouldn't try to solve data modeling issues with Dax. It is lousy at that.

If a human can figure out what I want to do, they can implement the behind the scenes logic to do stuff like this. Yes, I can go learn the row context and filter context logic of power pivots, but why should I have to?

So are you saying you just woke up one day, opened up Visual Studio and just started slamming out some c++ code? You intuitively and innately understood pointers, pointers to pointers, pointer arithmetic, operator overloading, function overloading, heap vs stack allocation, memory management, garbage collection, inheritance, polymorphism and a host of others things I don't remember any more? I doubt it. I'm sure you had to spend some time to read, study and learn it. So why is Dax any different? Again, it’s a new tool/new way to solve a problem. To learn how to use the new tool you must invest the time to learn it - just like with c++.

Did you even read the Definitive Guide to DAX?

Absolutely I have. No self-respecting Dax practitioner doesn't have a copy. And in my opinion it is the single best, most complete reference out there. But you do have to read it carefully because of the new way of thinking in Dax. And I do find it ironic that you labelled it "super basic" yet also indicate you didn't understand its content.

There's a reason why there are currently 600 people reading the excel forum and 5 people reading this one...when there are 100 times more people voting for prettier graphs and cooler chart actions, you can bet your desires will never be met.

I had a C.S. professor once that said the Unix command line was horrible and never should have been invented. Too hard. I was like "Um, what the hell? Are you serious???" I was astounded he would say such an idiotic thing. The unix command line is a thing of brilliance. Yes, the vast majority of people know how to drive a car, but get in the ****pit of an airplane and they have no idea what to do. But some car drivers are also pilots that can operate it. And I don't see airplanes going away anytime soon. For sure Power Pivot doesn’t have the same number of users as regular Excel and nor would I expect it to. Still a relatively new feature and power user only skill. I'm also sure a lot more people post to an Excel forum that say a c++ forum. Orders of magnitude larger user base. Excel/ Power Pivot is not where they are focusing their charting/graphing/dashboarding efforts. That energy is going into Power BI Desktop & their Power BI service. And more people post on Microsoft’s Power BI forum than over here.

I have no clue what an expression is, because it could be anything. My favorite was bullet point 3 The expression cannot use any function that scans a table or returns a table, including aggregation functions.

CALCULATE function reference doesn't specify a return type because that depends on the type returned by 'expression'. An 'expression' can return string, the various number types, boolean, datetime, and blank. CALCULATE has to return a scalar value which is why it says 'expression' cannot scan or return a table - because then it wouldn't. I agree saying "including aggregation functions" is confusing though. CALCULATETABLE returns a table but otherwise operates the same as CALCULATE.

I'm now operating in the business side of things, and for the problems I am faced with Power Query/Power Pivot/Dax is hands down the best reporting stack I have ever used. Financial statements, gl reconciliations, depreciation allocation, intercompany eliminations, benefit reconciliations, cost pool analysis, equipment charge rate generation, etc. It has made an incredible difference. Development is very quick, very flexible and extensible. But I still on occasion reach for things like Crystal Reports when it is the better tool for a respective problem.

I'm not angry at the people on this forum.

I hope not. Everyone on this forum is super nice and helpful. anvg responded to my earlier post pointing out a potential issue which I appreciate. I think I have learned just as much from the various forums and comment sections as I have from the books I have read. And my reply here is not to try and convert you or try to offend. Just to provide a different point of view. Good luck.

 

[martingaleh]

akice, your solution has one problem OR statement is written a single fact table. But in this topic it is about using OR for values is defined in two dimension tables. I think it is better to use UNION for such OR filter

= CALCULATE(SUM('fact'[value]);    UNION(
        CALCULATETABLE('fact'; FILTER('oneDim'; 'oneDim'[oneDim] = 1));
        CALCULATETABLE('fact'; FILTER('twoDim'; 'twoDim'[twoDim] = 2))
    )
)

Example file https://1drv.ms/x/s!AhKJrasHVtohiS3smw6ImJ49ErmI
Regards,

I looked at the sample file. I couldn't figure it out because I don't use Excel online, so I don't know if this is a concern, however I looked at the union function https://msdn.microsoft.com/en-us/library/dn802530.aspx documentation on the web and couldn't make sense of it. Many issues here:
[FONT=&quot]
Creates a union (join) table from a pair of tables. <---I don't know what this means. Union and join are different things in conventional language everywhere else but M where the equivalent is append and merge (microsoft being microsoft and all)
[/FONT][FONT=&quot]
This function is included in SQL Server 2016 Analysis Services (SSAS), Power Pivot in Excel 2016, and Power BI Desktop only. Information provided here is subject to change. <--- Well, I"m on 2013. My computer will upgrade again at the turn of the millenium. Someone was already fired moving us to Windows 8. We'll never hit windows 10

[/FONT][FONT=&quot]Duplicate rows are retained.<--- This isn't even the default behavior of T-SQL for a reason, why would it be the default behavior here.[/FONT]

[FONT=&quot]The two tables must have the same number of columns.[/FONT]
[FONT=&quot]Columns are combined by position in their respective tables. <---[/FONT]
This doesn't help me because my two dimensions are entirely different tables. (Isn't this generally true?) I know the Collie practice is not to make a frankentable, but if you don't need to otherwise interact with the dimension table, you must join it (merge it) in Power Query or else the "or" predicate across dimensions will lead to a cartesian product.

Thanks for trying. I think for now, my solution is the best. It is admittedly terrible.

Also, I just watched the new Seinfeld special streaming on Netflix. It just came out and its fresh in my head. It was great. But, https://www.sqlbi.com/tv/advanced-dax/ is so much funnier. It is not something we can discuss just by imagination. Comedy gold!

 

[anvg]

Really your case has and other ways of solution in SQL

select sum(amt) from fact left join onedim on ?? left join twodim on ?? where ondim.a = b or twodim.c = d group by all your other measures

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,

 

[Daniil]

martingaleh, your CROSSJOIN solution may look ugly to you, but it's actually not bad at all. If you want to avoid CROSSJOIN and UNION, here is what you can do:

OrSimple :=
CALCULATE (
    SUM ( 'fact'[value] ),
    FILTER (
        'fact',
        RELATED ( oneDim[oneDim] ) = 1
            || RELATED ( twoDim[twoDim] ) = 2
    )
)

In my view, this is as close as it gets to the SQL code you mentioned:

select
    sum(amt)
from
    fact
    left join onedim
        on ??
    left join twodim
        on ??
where
    ondim.a = b
    or twodim.c = d
group by
    all your other measures

---

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.

The issue here is filtering vs. iterating over a table.
When you use UNION as a filter, you are saying something like this:
"I want everything that is ether red, green, blue, red, or green."
Even though red and green are both mentioned twice, you are not counting them twice, because you are filtering with the criteria. This is why it looks like an equivalent if UNION in SQL, though it's still UNION ALL.

But when you use SUMX, the situation is a bit different:
"I want to sum values of red, green, blue, red, and green items."
In this case, you are double counting values, because this is what you are instructing DAX to do: go over red and green twice.

This topic reminded me of a concept of false friends