Create Measure using a different filter for two columns

[Ron_FS]

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!

[MFelix]

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]

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

[nickchobotar]

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 -

[AlbertoFerrari]

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]

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

[AlbertoFerrari · Accepted solution]

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

[Ron_FS]

Thanks @AlbertoFerrari @MFelix @OwenAuger for the quick feedback. 

@AlbertoFerrari THis solution worked well for me. I ended up needing to use the && for my filter instead of the ||, but the solution did cimpute faster. Thanks!!

[MFelix]

Hi @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