Forum Discussion

Ron_FS's avatar
Ron_FS
Frequent Visitor
9 years ago
Solved

Create Measure using a different filter for two columns

I have a table with 3 columns. I would like to create a measure that retreives the SUM of [Regular Hours] and filters by these rules:   Contains only "DIRECT" [Charge Type] Does not contain a "C" ...
  • AlbertoFerrari's avatar
    AlbertoFerrari
    9 years ago

    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

AlbertoFerrari's avatar
AlbertoFerrari
Most Valuable Professional
8 years ago

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

nickchobotar's avatar
nickchobotar
Skilled Sharer
8 years ago

Thanks a lot AlbertoFerrari    all clear and that's why you are the best.