Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |