Forum Discussion

Jensej's avatar
Jensej
Helper V
5 years ago
Solved

CASE WHEN THEN in Measure

Hello everyone!    Trying to create a measure with the IF or Switch function but can't make it work.    Can someone please help me to rewrite this so it works in a measure:   SUM( CASE WHEN a....
Need Help
  • themistoklis's avatar
    themistoklis
    5 years ago

    Jensej 

    Can you try the following formula:

     

    Measure = (
SUMX (
    Sheet1,    -- Table Name
    IF (
        Sheet1[accountNr1] = Sheet1[accountNr],
        CALCULATE(SUM ( Sheet1[Amount ] )),
        0)
    )
)
-
SUMX (
    Sheet1,    -- Table Name
    IF (
        Sheet1[accountNr2] = Sheet1[accountNr],
        CALCULATE(SUM ( Sheet1[Amount ] )),
        0)
    )
))*-1

     

Jensej's avatar
Jensej
Helper V
5 years ago

Thanks for your reply but i don't get the correct result. Are you sure about the code? Why use SUMX and not just SUM and why MAX?

amitchandak's avatar
amitchandak
Super User
5 years ago

Jensej ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

  • themistoklis's avatar
    themistoklis
    Community Champion
    5 years ago

    Jensej 

    Can you try the following formula:

     

    Measure = (
SUMX (
    Sheet1,    -- Table Name
    IF (
        Sheet1[accountNr1] = Sheet1[accountNr],
        CALCULATE(SUM ( Sheet1[Amount ] )),
        0)
    )
)
-
SUMX (
    Sheet1,    -- Table Name
    IF (
        Sheet1[accountNr2] = Sheet1[accountNr],
        CALCULATE(SUM ( Sheet1[Amount ] )),
        0)
    )
))*-1

     

    • Jensej's avatar
      Jensej
      Helper V
      5 years ago

      I made like this in end at it worked. 

       

      Measure = 
VAR _A = 
SUMX (
    Sheet1,    -- Table Name
    IF (
        Sheet1[accountNr1] = Sheet1[accountNr],
        CALCULATE(SUM ( Sheet1[Amount ] )),
        0)
    )
VAR _B = 
SUMX (
    Sheet1,    -- Table Name
    IF (
        Sheet1[accountNr2] = Sheet1[accountNr],
        CALCULATE(SUM ( Sheet1[Amount ] )),
        0)
    )

Return ( _A - _B ) * 1
    • Jensej's avatar
      Jensej
      Helper V
      5 years ago

      Thanks for your reply i will try but it seems there's a problem with to many ))

       

      🙂

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        5 years ago

        Hi Jensej ,

         

        If the problem persists,could you share the sample data or sample pbix and the result you expected?
        Please mask any sensitive data before uploading

         

        Best Regards,
        Liang
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.