Forum Discussion
Group data by before a date
I am trying to make a graph that shows all the devices that have appeared in the graph before a specific date.
For example, my original data is: with the first column as date and second as device name
| 7/21/2021 | b1 |
| 7/21/2021 | b2 |
| 7/22/2021 | b1 |
| 7/22/2021 | b3 |
| 7/23/2021 | b4 |
| 7/24/2021 | b1 |
| 7/24/2021 | b2 |
For 7/21/2021, the result should be b1, b2;
for 7/22/2021 the result should be b1, b2, b3. Although there is no record for b2 on 7/22/2021, there is a record for it on the previous day so we also include it here;
for 7/23/2021 the result should be b1, b2, b3,b4;
for 7/24/2021 the result should also be b1, b2, b3,b4.
Thank you so much for your help!
martcatnip Not exactly certain how you want the return data, but this is essentially a Complex Selector, The Complex Selector - Microsoft Power BI Community
Maybe something like:
Measure = VAR __SelectedDate = MAX('Table'[Date]) VAR __Table = FILTER(ALL('Table'),[Date]<=__SelectedDate) VAR __Devices = DISTINCT(__Table,[Device]) RETURN CONCATENATEX(__Devices,[Device],",")
1 Reply
martcatnip Not exactly certain how you want the return data, but this is essentially a Complex Selector, The Complex Selector - Microsoft Power BI Community
Maybe something like:
Measure = VAR __SelectedDate = MAX('Table'[Date]) VAR __Table = FILTER(ALL('Table'),[Date]<=__SelectedDate) VAR __Devices = DISTINCT(__Table,[Device]) RETURN CONCATENATEX(__Devices,[Device],",")