Forum Discussion

Jensej's avatar
Jensej
Icon for Helper V rankHelper 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.accountNr1 = b.accountNr THEN Amount ELSE 0 END
-
CASE WHEN a.accountNr2 = b.accountNr Then Amount ELSE 0 END) * - 1 as Total
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

     

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Jensej , You can try a measure like

    SUMX(filter(a[accountNr1] = max(b[accountNr])) , a[Amount]) - (SUMX(filter(a[accountNr2] = max(b[accountNr])) , a[Amount])*-1)

    • Jensej's avatar
      Jensej
      Icon for Helper V rankHelper 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?