Creating custom Column based on multiple conditions and comparison

[Anonymous]

Hello there ,

I am looking for help, how can we create a dax expression based on multiple condition and comparison ? , below is the scenario which i wish to obtain,

so for example we have data as below:

ID	Date	Satisfaction	ExpectedColumn
1	1/1/2018	SATISFIED
1	1/31/2018	NOT SATISFIED	1
2	2/10/2018	SATISFIED
2	3/20/2018	NOT SATISFIED
2	4/19/2018	SATISFIED
3	2/10/2018	SATISFIED
4	4/19/2018	SATISFIED
4	5/20/2018	SATISFIED
4	6/21/2018	NOT SATISFIED	1
5	6/22/2018	SATISFIED
5	8/23/2018	NOT SATISFIED	1
6	8/30/2018	NOT SATISFIED	1
7	9/7/2018	SATISFIED

I have three columns in my Data that are ID, DATE and SATISFACTION, what i am trying to get is 4th "Expected Column" which will be calculated in this way,

lets say for ID 1, if the Value in SATISFACTION column is "NOT SATISFIED" for the Latest date in date column, then expected Column should display 1.

As we can see in ID 1, ID 4, ID 5, ID 6.

kindly help me out on this one please,

Thank you,

Regards,

Malav

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

We can create a calculated column using the formula to meet your requirement.

excepted col =
VAR maxdate =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER ( ALL ( Table1 ), Table1[ID] = EARLIER ( Table1[ID] ) )
    )
RETURN
    IF (
        Table1[Date] = maxdate
            && Table1[Satisfaction] = "NOT SATISFIED",
        1,
        BLANK ()
    )

For more details, please check the pbix as attached.

https://www.dropbox.com/s/y3b8yxaurvsjkpo/Creating%20custom%20Column%20based%20on%20multiple%20conditions%20and%20comparison.pbix?dl=0

Regards,

Frank

[Anonymous]

Thank you very very much for your help @v-frfei-msft, this worked just great,
i am also having one another issue calculating the cumulative average, but thats a another question on discussion, still i am posting that link, please see if you can help me figure that out as well, 

link: 

https://community.powerbi.com/t5/Desktop/Calculating-Cumulative-total-of-average-using-DAX-expressio...

Much thanks @v-frfei-msft

Regards,

Malav