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 char...
  • 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 )
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.