Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I know, it sounds silly right?
So, we've got a start date, and end date and a client ID. We wanted an open cases measure. We followed the terribly useful https://powerpivotpro.com/2013/04/counting-active-rows-in-a-time-period-guest-post-from-chris-campbe... guidance, which works a treat.
One snag is the fact that it relies on no relationships between the date table and the source table means that any other metrics (count of starts, say) can't be controlled from the same filter. This has historically been a minor annoyance, but fixable within the design.
The problem now comes in that we've been asked to provide an associated Client ID list to go alongside the open clients list (so list of clients between active dates).
I'm wondering if there's a fix for this that allows for visual-level filtering from the graph itself (rather than a table with yet another group of slicers).
Solved! Go to Solution.
Good morning @JonPBNES
As is typical, turns out we were over thinking it. We didn't need USERELATIONSHIP, we can just read the date range selected, feed that to our filter measure and go. We do still need to compare Start Date and End Date to the date range.
OpenCaseFilter = VAR CompareStartDate = FIRSTDATE ( OpenCasesTable[Date] ) VAR CompareEndDate = LASTDATE ( OpenCasesTable[Date] ) RETURN IF ( CALCULATE ( COUNTROWS ( 'openfiltertest' ), FILTER ( openfiltertest, 'openfiltertest'[Start Date] <= CompareEndDate && 'openfiltertest'[End Date] >= CompareStartDate ) ) >= 1, "In", "Out" )
You can see the result we want even after I deleted the link between the two tables.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThe inactive relationships certainly don't nerf the calculation and will be useful for some other things we're trying to develop; but I'm struggling to think of how to apply them to allow a visual interaction.
If you were trying to filter table 1 based on the rows in table 2 you could do something like.
FilterFromT2 = IF ( ISBLANK ( CALCULATE ( COUNTROWS ( Table2 ), USERELATIONSHIP ( Table1[key], Table2[key] ) ) ), BLANK (), 1 )
Then you apply that as a visual level filter and set it to FilterFromT2 = 1
Oh, that's good. I'm falling down, possibly because of having to nest the USERELATIONSHIP functions within the calculation to replicate "open" (so, calling the two date fields).
I've got
OpenFilter = if(CALCULATE( COUNT(my table[Client_ID]), USERELATIONSHIP(ActiveCasesDateTable[Date],my table[start_date]), FILTER(my table, my table[start_date]<=LASTDATE(ActiveCasesDateTable[Date])) && (USERELATIONSHIP(my table[start_date],ActiveCasesDateTable[Date]) &&
FILTER(my table,my table[end_date]>=LASTDATE(ActiveCasesDateTable[Date]) )))>=1,"In",Out")
Which is throwing up that annoyingly vague "function filter has been used in a TRUE/FALSE expression that is used in a table filter expression, this is not allowed" error.
Any further thoughts hugely appreciated!
Hi @JonPBNES ,
If I understand your need correctly, you don't need to have a list based on the active start and end date, correct, taking into account that you have no relationship between the two tables.
I know I suggested to use the USERELATIONSHIP but looking at your code and reading once again your initial post believes that the best way is to make a measure as @jdbuchanan71 suggested and use it on the filter visuals that way when you use your slicer the other visual will be also updated.
Can you share sample data?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
I had a go at that measure and got a part of the way there. It is returning cases that both started and finished within the period, as opposed to those that are open in the quarter (ie has a start date before period) and I can't quite work out why.
Intuitively I feel it needs to use both relationships somehow, but then I start getting lost.
I have simplified a test data model, recreated working to date. This is here
Thanks again both for your time!
Good morning @JonPBNES
I want to confirm that an open count for a month is one where the Start Date is <= the end of the month and the End Date >= the first of the month. The three Client ID's below would all be considered open in Jan-2018 yes?
If so, your compare is looking only at the last day of the quarter even when looking at the End Date so it is ignoring anything that ended in the quarter. Client ID 1013446 for example ended 3/20/2018 but your count is not picking it up in Q1-2018
If you change your measure slightly I think you will get the result you are looking for.
Cases Open = CALCULATE ( COUNT ( openfiltertest[Client ID] ), FILTER ( openfiltertest, openfiltertest[Start Date] <= LASTDATE ( OpenCasesTable[Date] ) && openfiltertest[End Date] >= FIRSTDATE ( OpenCasesTable[Date] ) ) )
The chart below has your original measure and the updated measure side by side to illustrate the difference.
Of course, I re-keyed my count measure wrong didn't I. What an eejit!
The issue still seems to be persisting into the drillthrough measure, however.
Good morning @JonPBNES
As is typical, turns out we were over thinking it. We didn't need USERELATIONSHIP, we can just read the date range selected, feed that to our filter measure and go. We do still need to compare Start Date and End Date to the date range.
OpenCaseFilter = VAR CompareStartDate = FIRSTDATE ( OpenCasesTable[Date] ) VAR CompareEndDate = LASTDATE ( OpenCasesTable[Date] ) RETURN IF ( CALCULATE ( COUNTROWS ( 'openfiltertest' ), FILTER ( openfiltertest, 'openfiltertest'[Start Date] <= CompareEndDate && 'openfiltertest'[End Date] >= CompareStartDate ) ) >= 1, "In", "Out" )
You can see the result we want even after I deleted the link between the two tables.
That's cracked it! Thank you so much for your time.
We don't use VAR anywhere near enough, I'm realising. (there's probably a world cup joke in there somewhere)
I think you only need to call the use relationship once in the calculate and you can pull the compare value into a VAR to convert it to a scalar. Give this a try:
CP OpenFilter = VAR CompareDate = LASTDATE ( ActiveCasesDateTable[Date] ) RETURN IF ( CALCULATE ( COUNTROWS ( 'my table' ), FILTER ( ALL ( 'my table'[start_date], 'my table'[end_date] ), 'my table'[start_date] <= CompareDate && 'my table'[end_date] >= CompareDate ), USERELATIONSHIP ( ActiveCasesDateTable[Date], 'my table'[start_date] ) ) >= 1, "In", "Out" )
If this doesn't work, can you share your model for further testing?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
146 | |
87 | |
66 | |
52 | |
46 |
User | Count |
---|---|
215 | |
90 | |
83 | |
66 | |
58 |