Forum Discussion
Create Measure using a different filter for two columns
I personally like MFelix solution, simple and clean. It could be made a bit simpler (and faster) by avoiding filtering the table and, instead, filter only the two columns needed:
Measure = CALCULATE ( SUM ( 'Table'[Regular Hours] ); FILTER ( ALL ( 'Table'[Charge Type], Table[Profit Center] ), 'Table'[Charge Type] = "Direct" || 'Table'[Profit Center] <> "C" ) )Remember: filtering a table is nearly always a bad idea, not only performance-wise, but also from a semantical point of view. I wrote an article about this some time ago: https://www.sqlbi.com/articles/context-transition-and-expanded-tables/. The example in the article is a somewhat complex one, but it demonstrates how filtering a table might lead to surprising (that is, wrong) results.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
Hi Ron_FS,
The correct measure is:
Measure =
CALCULATE (
SUM ( 'Table'[Regular Hours] );
FILTER (
'Table';
'Table'[Charge Type] = "Direct"
|| 'Table'[Profit Center] <> "C"
)
)
As you can see below it gives you values in the fields you want:
Regards,
MFelix
Hi Ron_FS
Two idea's based on MFelix's solution, that may improve performance (you mentioned that it was computationally expensive), only because they deal with smaller tables within the CALCULATE filter argument:
Measure using SUMMARIZE v1 = CALCULATE ( SUM ( 'Table'[Regular Hours] ), FILTER ( SUMMARIZE ( 'Table', 'Table'[Charge Type], 'Table'[Profit Center] ), 'Table'[Charge Type] = "DIRECT" || 'Table'[Profit Center] = "Corporate" ) )Measure using SUMMARIZE v2 = VAR Filter_ChargeType = CALCULATETABLE ( SUMMARIZE ( 'Table', 'Table'[Charge Type], 'Table'[Profit Center] ), TREATAS ( { "DIRECT" }, 'Table'[Charge Type] ) ) VAR Filter_ProfitCenter = CALCULATETABLE ( SUMMARIZE ( 'Table', 'Table'[Charge Type], 'Table'[Profit Center] ), TREATAS ( { "Corporate" }, 'Table'[Profit Center] ) ) VAR Filter_Union = UNION ( Filter_ChargeType, Filter_ProfitCenter ) RETURN CALCULATE ( SUM ( 'Table'[Regular Hours] ), KEEPFILTERS ( Filter_Union ) )I personally like MFelix solution, simple and clean. It could be made a bit simpler (and faster) by avoiding filtering the table and, instead, filter only the two columns needed:
Measure = CALCULATE ( SUM ( 'Table'[Regular Hours] ); FILTER ( ALL ( 'Table'[Charge Type], Table[Profit Center] ), 'Table'[Charge Type] = "Direct" || 'Table'[Profit Center] <> "C" ) )Remember: filtering a table is nearly always a bad idea, not only performance-wise, but also from a semantical point of view. I wrote an article about this some time ago: https://www.sqlbi.com/articles/context-transition-and-expanded-tables/. The example in the article is a somewhat complex one, but it demonstrates how filtering a table might lead to surprising (that is, wrong) results.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.comHi AlbertoFerrari,
First' of all let me tell you that since I started working with Power BI and DAX I have follow SQLBI and have learn a lot from your courses, my only regret is not being abble (for now) to assist one of your workshops.
I have made a sample with only 3 columns and 5 lines that's why I used the filter on the full table and didn't though about the impacts on a much bigger table, will add this note to my best practices.
Thank you for the approval of my solution, always good to have one of the best in DAX to look at our code and give it's thumbs up. :)
Regards,
MFelix
Hi OwenAuger
I really like your second logic. Since Ron_FS wants an AND not OR as a condition then your logic works perfectly with the INTERSECT() instead of UNION(). In terms performance, I would imagine this code should fly since we have no explicit and I hope implicit iterators here . AlbertoFerrari would you agree there is no implict iteration here ?
Owen's KEEPFILTERS() = VAR Filter_Charge = CALCULATETABLE( SUMMARIZE(Table1, Table1[Charge Type], Table1[Profit Center]), TREATAS({"DIRECT"}, Table1[Charge Type]) ) VAR Filter_ProfitCenter = CALCULATETABLE( SUMMARIZE(Table1, Table1[Charge Type], Table1[Profit Center]), TREATAS({"C"}, Table1[Profit Center]) ) VAR Filter_Union = INTERSECT(Filter_Charge, Filter_ProfitCenter) RETURN CALCULATE(SUM(Table1[Regular Hours]), KEEPFILTERS(Filter_Union))
Thanks, N -No. The formula has only several drawbacks: it requires two scans of the fact table (the two SUMMARIZE statements) and potentially TREATAS is resolved by FE, reducing the chances for the optimizer to build a good query plan.
Besides, it is equivalent to a much easier version:
CALCULATE ( SUM ( Table1[Regular Hours] ), KEEPFILTERS ( Table1[Charge Type] = "DIRECT" ), KEEPFILTERS ( Table1[Profit Center] = "C" ) )I see no reason to make your life harder using so many lines of code when you can write it in 4 lines only. You need more coding if you want to produce an OR statement, whereas AND is already included in CALCULATE by providing multiple filters.
Iteration is NOT a problem in DAX. Iteration might lead to bad performance but, by itself, it is not an issue at all. Iteration is present everywhere, always remember that SUM is syntax sugaring for SUMX.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
