SQL CASE Statement to DAX Measure

[apollo89]

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!

[lucas021]

Hello @jmalone, I have used your solution with selected value to my problem, but as happenned to @apollo89 the totals were incorrect.

I could't use calculate because i need to indicate an alternative value, in case of don't match the conditions.

Current Portfolio 70% =

IF (

AND (

SELECTEDVALUE ( Company[Company Code] ) = "042",

SELECTEDVALUE ( 'Key Accounts (Enrichment)'[Key Accounts Level 1] ) <> "AMAZON"

),

[Pending CM Global Amount (net)] * 0.7,

[Pending CM Global Amount (net)]

)

[jmalone]

@lucas021 could you please provide a screenshot of your data model, and the definition of [Pending CM Global Amount (net)] measure?

It's hard to say how your measure should look without knowing which tables are being used. You may want a SUMX() function with an IF statement, but maybe another function would be better.

[jmalone]

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

[apollo89]

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? 

[jmalone · Accepted solution]

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

[apollo89]

This worked! Thanks @jmalone.

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

[jmalone]

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