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,
I have read several posts quite similar to what I am trying to do but can't find a solution.
I have a table called Records which has a Start Date and an End Date for each record.
Record # | Start DateKey | End DateKey |
1 | 20171201 | 20180102 |
2 | 20180101 | 20180102 |
3 | 20180102 | 20180110 |
I want to use a slicer based on a DateKey table to select and display those records where the StartDate is after the start of the period and the EndDate is before the end of the period.
I can count the number of these records with a measure that looks like this:
No of open&closed = CALCULATE(COUNT(Records[End DateKey]),FILTER(Records,
AND (MIN(DateKey[DateKey])<=Records[End DateKey],
MAX(DateKey[DateKey])>=Records[End DateKey])))
where I already have a relationship between DateKey[DateKey] and Records[Start DateKey].
But how do I display the records resulting from this kind of filter?
Thanks,
Nigel
Solved! Go to Solution.
Hi @NigelS,
1. You don't need to create the relationship between the two tables.
2. Then you could create a measure like below.
Measure = VAR mindate = MIN ( 'Calendar'[Date] ) VAR maxdate = MAX ( 'Calendar'[Date] ) RETURN IF ( MIN ( 'Records'[Start DateKey] ) >= mindate && MAX ( 'Records'[End DateKey] ) <= maxdate, 1, 0 )
3. Create the Between Slice with the Calendar [Date].
4. Add the measure to visual level filters in the Filter pane like the picture below.
In addition, you could have a reference of my pbix file.
Hope it can help you!
Best Regards,
Cherry
Hi @NigelS,
1. You don't need to create the relationship between the two tables.
2. Then you could create a measure like below.
Measure = VAR mindate = MIN ( 'Calendar'[Date] ) VAR maxdate = MAX ( 'Calendar'[Date] ) RETURN IF ( MIN ( 'Records'[Start DateKey] ) >= mindate && MAX ( 'Records'[End DateKey] ) <= maxdate, 1, 0 )
3. Create the Between Slice with the Calendar [Date].
4. Add the measure to visual level filters in the Filter pane like the picture below.
In addition, you could have a reference of my pbix file.
Hope it can help you!
Best Regards,
Cherry
Hi @v-piga-msft,
Many thanks for your solution which has worked out perfectly for me. The reference file was very helpful. I made one small modification to remove the "All" in the measure in the fact table so as I could have other slicers applied in the same visual.
Regards,
Nigel
Hi Cherry,
Thanks for your reply and making the modification to the measure so that it doesn't rely on the relationship. That all makes sense, but it doesn't address the key problem I have which is that I need to see the resulting records in a table visualisation.
The measure shows me the count, but how can I actually see the filtered records?
Thanks,
Nigel
Hi again Cherry,
Please ignore my last message. I hadn't fully understood your example. Now I am looking again more closely and I see how you display the records also. I need to test it further with the real report now but this looks very promising thanks.
Regards,
Nigel
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |