CASE WHEN THEN in Measure

[Jensej]

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

[amitchandak]

@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]

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]

@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 · Accepted solution]

@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]

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]

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

🙂

[V-lianl-msft]

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.