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

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

7 Replies

  • jmalone's avatar
    jmalone
    Resolver III
    8 years ago
    TEST =
IF (
    SELECTEDVALUE ( 'TableName'[VALUE_TYPE] ) = "070"
        && SELECTEDVALUE ( 'TableName'[VALUE_TYPE_DETAIL] ) = "06",
    SUM ( 'TableName'[AMOUNT] ),
    0
)
    • apollo89's avatar
      apollo89
      Helper II
      8 years ago

      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's avatar
        jmalone
        Resolver III
        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.