cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

SQL CASE Statement to DAX Measure

Hi All,

 

Need help to replicate the below Case statement into a DAX measure:

 

SUM(
        CASE
                WHEN VALUE_TYPE = '070' AND VALUE_TYPE_DETAIL = '06' THEN AMOUNT
                ELSE 0
        END
) AS TEST

 

Here VALUE_TYPE and VALUE_TYPE_DETAIL are columns with String data type and AMOUNT is a column with float data type.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II
Resolver II

Re: SQL CASE Statement to DAX Measure

Yes, sorry I misunderstood your goal. Try this, it should perform much better as well.

 

TEST =
CALCULATE (
    SUM ( 'TableName'[Amount] ),
    'TableName'[VALUE_TYPE] = "070",
    'TableName'[VALUE_TYPE_DETAIL] = "06"
)

View solution in original post

5 REPLIES 5
Highlighted
Resolver II
Resolver II

Re: SQL CASE Statement to DAX Measure

TEST =
IF (
    SELECTEDVALUE ( 'TableName'[VALUE_TYPE] ) = "070"
        && SELECTEDVALUE ( 'TableName'[VALUE_TYPE_DETAIL] ) = "06",
    SUM ( 'TableName'[AMOUNT] ),
    0
)
Highlighted
Helper II
Helper II

Re: SQL CASE Statement to DAX Measure

Thank you for your response @jmalone.

 

However this gives me a 0 in each row as well as a 0 in the grand total.

Moreover, this measure was very slow to populate (I am working with a million records)

Any ideas? 

Highlighted
Resolver II
Resolver II

Re: SQL CASE Statement to DAX Measure

Yes, sorry I misunderstood your goal. Try this, it should perform much better as well.

 

TEST =
CALCULATE (
    SUM ( 'TableName'[Amount] ),
    'TableName'[VALUE_TYPE] = "070",
    'TableName'[VALUE_TYPE_DETAIL] = "06"
)

View solution in original post

Highlighted
Helper II
Helper II

Re: SQL CASE Statement to DAX Measure

This worked! Thanks @jmalone.

 

Just out of curiousity, why didn't your previous solution work?

Highlighted
Resolver II
Resolver II

Re: SQL CASE Statement to DAX Measure

There are two components:

1) Incorrect totals

2) Slow performance

 

The totals were incorrect because the first method checks for a condition where the SUM() calculation only happens when those VALUE_TYPE and VALUE_TYPE_DETAIL conditions were met. The engine essentially performed an IF>THEN check for each piece of your visual (ie each row of your table visual), and performed the calc when applicable.

Since the "Total" row had more than the '070' & '06' values, the condition check was = FALSE, and you got no SUM([Amount]).

As a test, if you were to filter (via slicers or page-level filters) the report to show these 070/06 values, the total row should populate with an amount, not 0.

 

The performance improved because the CALCULATE() function takes full advantage of the engine. The formula filters your table in memory, then applies a SUM() to this virtual table. The engine is designed to perform this type of operation very quickly, even on millions of rows.

The Total row also gives you the correct amount because of the virtual filter that has been applied.

 

So it's a conditional if/then check (slow) vs. an aggregation on a virtual filtered table (optimized).

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors