Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Changing Values Based on a Condition

Hi!

 

I'm having trouble with writing this DAX command. I'm trying to change a value based on the value in another column. For example, I attached an example power bi file and a picture of the chart.

 

For each transaction type, a number is assigned in a Status column, for Deaths,Dropped and Resignations its -1 and for Elected and Reinstated its 1.

I'm trying to have a total that includes everyone including the people who died (without counting the same person twice). I also have to show a breakdown of this number in a chart that is seperated by transaction. 

I was wondering if there was a way for me to have this count but if a person who was previously elected and is now dead, if there was a way for me to switch the value in the Status column for them being elected to -1 (instead of 1) and death to 1 (instead of -1)

 

https://drive.google.com/file/d/1eW3S5ZDKbShKp8wYvwYJZXUyo4oY91FM/view?usp=sharing 

 

Thank you for your help!!! Let me know if you have questions!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below measure.

     

    new total: = 
VAR currenttransaction =
    MAX ( data[Transaction] )
VAR electedlist =
    SUMMARIZE (
        FILTER (
            SUMMARIZE ( ALL ( data ), data[name], data[date], data[Transaction] ),
            data[Transaction] = "Elected"
        ),
        data[name]
    )
VAR deathslist =
    SUMMARIZE (
        FILTER (
            SUMMARIZE ( ALL ( data ), data[name], data[date], data[Transaction] ),
            data[Transaction] = "Deaths"
        ),
        data[name]
    )
VAR bothlist =
    INTERSECT ( electedlist, deathslist )
VAR currentalltable =
    SUMMARIZE ( ALL ( data ), data[name], data[Transaction], data[Status] )
VAR exceptbothlistname =
    FILTER (
        SUMMARIZE ( ALL ( data ), data[name], data[Transaction], data[Status] ),
        NOT ( data[name] IN bothlist )
    )
RETURN
    SUMX (
        FILTER ( exceptbothlistname, data[Transaction] = currenttransaction ),
        data[Status]
    )
        + SUMX ( FILTER ( data, data[name] IN bothlist ), data[Status] * -1 )

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below measure.

     

    new total: = 
VAR currenttransaction =
    MAX ( data[Transaction] )
VAR electedlist =
    SUMMARIZE (
        FILTER (
            SUMMARIZE ( ALL ( data ), data[name], data[date], data[Transaction] ),
            data[Transaction] = "Elected"
        ),
        data[name]
    )
VAR deathslist =
    SUMMARIZE (
        FILTER (
            SUMMARIZE ( ALL ( data ), data[name], data[date], data[Transaction] ),
            data[Transaction] = "Deaths"
        ),
        data[name]
    )
VAR bothlist =
    INTERSECT ( electedlist, deathslist )
VAR currentalltable =
    SUMMARIZE ( ALL ( data ), data[name], data[Transaction], data[Status] )
VAR exceptbothlistname =
    FILTER (
        SUMMARIZE ( ALL ( data ), data[name], data[Transaction], data[Status] ),
        NOT ( data[name] IN bothlist )
    )
RETURN
    SUMX (
        FILTER ( exceptbothlistname, data[Transaction] = currenttransaction ),
        data[Status]
    )
        + SUMX ( FILTER ( data, data[name] IN bothlist ), data[Status] * -1 )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you!! 

      I was wondering how I would factor in the other transaction types like resignations and reinstatements. Also, I was wondering if there was a way for me to get the first transaction type of a person. Would I do Min(data[Transaction])?

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi,

        Thank you for your feedback.

        I think you can use the similar way to identify the type.

        And in a general situation, MIN function is used to get the first date of the transaction.

        Thanks.