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" [Profit Center]

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I have tried several different options, but do not seem to be getting accurate results. I also found that CALCULATE was computationally expensive. In addition, I'm not sure whether to use the && or || in this case. I don't need the two conditions to both be met for each row. Just one, or the other, or both. And... maybe I'm going about this all wrong! I have looked thorugh the forum, and obtained the following options from that process, but still think I am missing something.

 

Measure = CALCULATE(SUM(Table[Regular Hours]), FILTER(Table, Table[Charge Type] = "DIRECT" || Table[Profit Center] <> "Corparate")

 

Measure = SUMX(FILTER(Table, Table[Charge Type] = "DIRECT" || Table[Profit Center] <> "Corporate"),Table[Regular Hours])

 

Can anyone assist me? Many thanks in advance!

  • 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

8 Replies

  • MFelix's avatar
    MFelix
    Super User
    9 years ago

    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

    • OwenAuger's avatar
      OwenAuger
      Super User
      9 years ago

      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 ) )

       

      • AlbertoFerrari's avatar
        AlbertoFerrari
        Most Valuable Professional
        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