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.
My problem can be split into two I suppose. The original issue, I would like to have a Calendar filter my main contract table, such that if I select a date then only contracts which started before and finished after that date show up.
That's easy enough to do and apply in an individual measure using filters. But I am going to reuse this in many measures, which leads to me to two questions:
1. Is there a way to define a table filter which can be reused in multiple measures. E.g. A measure which is a filter and I define it once and reuse it in many subsequent measures (I know you can't return a table in a measure but that's how I imagine it.)?
2. For this specific case, is there a better way of defining this relationship between the Calendar table and the Contract table? Maybe I don't need to use measures/ filters at all?
I think I know the answer to both of these questions but it feels like reusing the same filter code in a dozen or so measures is bad practice and will be a pain if I need to go back through and change anything. It would be much better to just define the filter once and reuse it where it's needed.
Thanks in advance,
WG
Solved! Go to Solution.
So, having thought about it for 10 more minutes, I decided to Create a Measure which doesn't return a filter itself but can be used in a filter expression. Therefore the logic still gets reused.
The filter returns True if the Max date in the Calendar is > than the Contract start date and the Min Date of the Calendar is < the Contract End Data.
So if the Calendar isn't filtered, all contracts come through.
If the Calendar is filtered to say a month then all Contracts which are active for any time in that Month come through.
If you have selected a single Date then exactly contracts which are active on that date will come throuh.
Thank you for sharing, I think the above measure is very effective. Could you please mark your post as Answered since it is working now? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.
So, having thought about it for 10 more minutes, I decided to Create a Measure which doesn't return a filter itself but can be used in a filter expression. Therefore the logic still gets reused.
The filter returns True if the Max date in the Calendar is > than the Contract start date and the Min Date of the Calendar is < the Contract End Data.
So if the Calendar isn't filtered, all contracts come through.
If the Calendar is filtered to say a month then all Contracts which are active for any time in that Month come through.
If you have selected a single Date then exactly contracts which are active on that date will come throuh.
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |