Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to 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.
Regards,
Frank
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.
Regards,
Frank
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
213 | |
89 | |
77 | |
66 | |
60 |