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

     

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