Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I would like to create a subset of a larger table based on date parameters I would insert via a filter.
My main table has the structure as seen here:
Customer | Attribute A | Attribute B | Date Since | Date Until |
A | Yes | qrv | 01.11.2021 | |
B | No | abd | 05.09.2023 | |
C | No | bbb | 20.09.2023 | |
A | No | qrv | 01.01.2016 | 31.10.2021 |
B | No | abc | 01.03.2019 | 05.09.2023 |
C | Yes | abc | 23.04.2019 | 19.09.2023 |
D | Yes | qrv | 27.03.2020 | 09.10.2021 |
If there is no value in the column Date Until, it means that this entry is still current and the customer is active (unlike customer D, for example)
Now, let's say the user would select the date December 11 2021 via the filter. I would now like to have a subset of the table above which would be, given the selected date, be
Customer | Attribute A | Attribute B |
A | Yes | qrv |
B | No | abc |
C | Yes | abc |
The reason why I would like to have a subset of the table above is that I would like to use the different columns of this subset of the main table to use in other measures.
Can anyone help me in finding the right measure to create the subset as described above?
Currently, I tried the following measure but I am not quite sure whether or how I can even access the different columns of the variable subsets in other measures
Subset =
var subsets=
FILTER(
maintable,
maintable[Date Since]>Date(2021,12,11) && maintable[Date Until]<Date(2021,12,11)
)
var colum=
COUNTROWS(
subsets
)
RETURN
colum
Solved! Go to Solution.
Hi @ThomasSan
Thanks @lbendlin for your timely reply. Here are my additions:
You can create a measure as follows.
1 =
IF (
MAX ( [Date Since] ) <= SELECTEDVALUE ( 'Date'[Date] )
&& MAX ( [Date Until] ) >= SELECTEDVALUE ( 'Date'[Date] )
|| MAX ( [Date Since] ) <= SELECTEDVALUE ( 'Date'[Date] )
&& MAX ( [Date Until] ) = BLANK (),
1,
0
)
Put the measure into the filter so that the visual only shows data where the measure is equal to 1.
Is this the result you expect?
Best Regards,
Yulia Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ThomasSan
Thanks @lbendlin for your timely reply. Here are my additions:
You can create a measure as follows.
1 =
IF (
MAX ( [Date Since] ) <= SELECTEDVALUE ( 'Date'[Date] )
&& MAX ( [Date Until] ) >= SELECTEDVALUE ( 'Date'[Date] )
|| MAX ( [Date Since] ) <= SELECTEDVALUE ( 'Date'[Date] )
&& MAX ( [Date Until] ) = BLANK (),
1,
0
)
Put the measure into the filter so that the visual only shows data where the measure is equal to 1.
Is this the result you expect?
Best Regards,
Yulia Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
let's say the user would select the date December 11 2021 via the filter. I would now like to have a subset of the table
That's it. That's all you need. Applying a filter will give you the subset of the table.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
26 | |
20 | |
14 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
17 | |
17 |