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

     

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?

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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
      Icon for Community Champion rankCommunity 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
        Icon for Helper V rankHelper 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