Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I need a dax measure wich has this rules below:
Count of patients whose lost 5% of weight in 6 months*
1 - First the patients answer a question about their weight in the program "A" (This is considerated the first date of weight)
2 -Then, a lot of times they measure their weights
3 - I want to know their results after 6 months
4 - If they didn't measure their weight after 6 months, I want to see the result after 7 months (DON'T Matter the type of program)
5 - Now, only the count of those whose lost 5% of weight
Date | Patient | Program | Weight |
01/01/2018 | Maria | A | 60 |
02/01/2018 | Johnny | A | 70 |
03/01/2018 | Carlos | A | 50 |
01/02/2018 | Maria | B | 60 |
02/03/2018 | Johnny | D | 70 |
03/04/2018 | Carlos | G | 50 |
01/06/2018 | Maria | F | 56 |
02/07/2018 | Johnny | F | 66 |
03/06/2018 | Carlos | H | 50 |
My count should be returned as 2, because Maria and Johnny lost >= 5% of weigth.
PS: I count Jonny, because I'd search for him as I didn't find him after 6 months
Can someone help me?
In your sample The count will be 0?
Maria 60 - 58 => 3.3%
Johnny 70 - 69 = 1.4%
Carlos 50 - 50 = 0.0%
Hi, edited.
Hi, you can use 2 measures:
%Change = VAR _FirstDate = CALCULATE ( FIRSTDATE ( Table1[Date] ), Table1[Program] = "A" ) VAR _WeightinFirstDate = CALCULATE ( VALUES ( Table1[Weight] ), Table1[Date] = _FirstDate ) VAR _6monthslater = CALCULATE ( FIRSTDATE ( Table1[Date] ), Table1, Table1[Date] >= EDATE ( _FirstDate, 5 ) ) VAR _Weightafter6months = CALCULATE ( VALUES ( Table1[Weight] ), Table1[Date] = _6monthslater ) VAR _PercentOfChange = DIVIDE ( ( _Weightafter6Months - _WeightinFirstDate ), _Weightafter6Months ) RETURN _PercentOfChange
CountLostWeight = SUMX ( VALUES ( Table1[Patient] ), IF ( [%Change] <= -0.05, 1, 0 ) )
Regards
Victor
Thanks Victor. Helped me a lot.
I will mark your answer as solution.
Just another doubt: Can I check in month 7 if I don't find the patient in month 6? How would I do this?
The formula should be work with month 7 also.
I tested with your data sample
Regards
Victor
The measure is looking foward 6 months after.
01/01/2018 will check 01/06/2018, but i'd like to see until the whole month (last day of month)
I don't want to see exactly six months and days later, but a range of that month taking the min date if the weight ocurred more than once
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |