Changing Values Based on a Condition

[Anonymous]

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

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]

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]

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.