Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hi folks,
I have this table, may I know how can use DAX to obtain the results in the 2 black columns?
a) Sum Gap by Distinct Date by Member ID,
b) Count Distinct LastDate by Member ID
c) Sum Gap by Distinct Date by Member ID, (for the most recent 3 dates), desired output = (1 + 5 + 3 ) = 9
d) Count Distinct LastDate by Member ID, (for the most recent 3 dates), desired output = (17 Jul, 12 Jul, 10 Jul) = 3
Data attached for your convenience, sorry I don't know how to put the header properly.
4748 | DingDong | 18 July 2018 | 17 July 2018 | 1 | 6 |
4748 | DingDong | 18 July 2018 | 17 July 2018 | 1 | 6 |
4748 | DingDong | 18 July 2018 | 17 July 2018 | 1 | 6 |
4748 | DingDong | 17 July 2018 | 12 July 2018 | 5 | 6 |
4748 | DingDong | 17 July 2018 | 12 July 2018 | 5 | 6 |
4748 | DingDong | 17 July 2018 | 12 July 2018 | 5 | 6 |
4748 | DingDong | 17 July 2018 | 12 July 2018 | 5 | 6 |
4748 | DingDong | 12 July 2018 | 10 July 2018 | 2 | 6 |
4748 | DingDong | 10 July 2018 | 09 July 2018 | 1 | 6 |
4748 | DingDong | 10 July 2018 | 09 July 2018 | 1 | 6 |
4748 | DingDong | 10 July 2018 | 09 July 2018 | 1 | 6 |
4748 | DingDong | 09 July 2018 | 08 July 2018 | 1 | 6 |
4748 | DingDong | 09 July 2018 | 08 July 2018 | 1 | 6 |
4748 | DingDong | 08 July 2018 | 6 | ||
1572 | VK | 19 July 2018 | 15 July 2018 | 4 | 4 |
1572 | VK | 19 July 2018 | 15 July 2018 | 4 | 4 |
1572 | VK | 15 July 2018 | 10 July 2018 | 5 | 4 |
1572 | VK | 10 July 2018 | 07 July 2018 | 3 | 4 |
1572 | VK | 07 July 2018 | 4 | ||
1572 | VK | 07 July 2018 | 4 |
Solved! Go to Solution.
@Anonymous
Try this column
Sum Gap by Distinct Date = SUMX ( SUMMARIZE ( FILTER ( Table1, Table1[Member ID] = EARLIER ( Table1[Member ID] ) ), Table1[Date], Table1[Gap] ), [Gap] )
Hi @Anonymous,
b)
Count Distinct LastDate = CALCULATE ( DISTINCTCOUNT ( Sheet2[LastDate] ), FILTER ( ALLEXCEPT ( Sheet2, Sheet2[Member ID] ), Sheet2[LastDate] <> BLANK () ) )
c) Which rows are the most recent 3 dates? I cannot understand the desired output.
d) It looks like the most recent 3 dates of LastDate are 17 July 2018, 15 July 2018 and 12 July 2018. But why do you say "17 Jul, 12 Jul, 10 Jul"? Besides, since you want to Count Distinct LastDate for the most recent 3 dates, the result is always 3, right?
Regards,
Yuliana Gu
@Anonymous
Try this column
Sum Gap by Distinct Date = SUMX ( SUMMARIZE ( FILTER ( Table1, Table1[Member ID] = EARLIER ( Table1[Member ID] ) ), Table1[Date], Table1[Gap] ), [Gap] )
wow you're awesome! that solved part a)
I managed to get part b)
CALCULATE ( DISTINCTCOUNT ( Rating[LastDate] ), FILTER ( ALL ( Rating ), Rating[Member ID] = EARLIER( Rating[Member ID] ) ) )
Would greatly appreciate if you can also solve c)
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |