Forum Discussion

apollo89's avatar
apollo89
Helper II
8 years ago
Solved

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              ...
Need Help
  • jmalone's avatar
    jmalone
    8 years ago

    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"
)
lucas021's avatar
lucas021
New Member
5 years ago

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's avatar
jmalone
Resolver III
5 years ago

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.