Where [Last_day_this_month] = ENDOFMONTH('Dates'[Date])
At issue is the ability to filter [checked_out] based on the active relationship and [returned] based on the inactive relationship in filtering the same list…. Or at least that is where my pursuit has taken me. I would appreciate suggestions on a solution to this.
P.S. It seems likely that a similar scenario has been covered elsewhere, so happy to simply have a pointer to a discussion.
I wish it were so. At issue is that I must use the primary relationship (books[checked_out] < [Last_day_this_month]) for the first filter criteria and the secondary relationship (books[returned] > [Last_day_this_month]) for the other filter. The USERELATIONSHIP works for the CALCULATE expression, not the FILTER expression so I can't have both relationships... or at least I don't yet see how.
I choose a slightly different approach, I created a new table using this DAX statement:
not present =
var maxDate = MAX('Dates'[Date])
var returndate = [Return Date]
var enddate = IF(ISBLANK(returndate) , maxDate , returndate)
DATESBETWEEN('Dates'[Date] , [Borrow Date] , enddate)
This creates a table that expands your table 'Sample Data' and creates a row for each ID (a book i guess), please don't be afraid by creating millions of rows, the performance will be much better as this can leverage the way faster storage engine during measure evaluation instead of the slower formula engine. as just a single column is involved, the "new" expanded date column.
I also creaed an books table (more out of habit) using this DAX
The measure "Still out new approach" is based on this reasoning reasoning: count the books that are "not present" on the "last day of the month", for this I created this measure:
still out new approach =
var lastDayOfTheMonth = [Last_day_this_month]
, [Date] = lastDayOfTheMonth
This allows to create a report like this:
Basically you can hide the table "not present" from the user, as it is just used for calculations. And it's not related to your Dates table:
I'm not sure if you still need the inactive relationship between the tables "Dates" and "Sample_data".
Hamburg - Germany If I answer you question, please mark my post as solution, this will also help others. Proud to be a Datanaut!
Thanks! I created your Tables and Measures as suggested, confirmed by same results you posted. To test the validity I created a visual with a Table that contains 3 elements of Sample_Data: Borrow Date, ID, and Return Date. I applied two filters to the visual (and no other filters) Borrow Date is before 11/1/2018 12:00 AM and Return Date is (after 10/31/2018 11:59 PM OR is blank.) I would have expected that the Count of IDs (6,285) would equal the "still out new apporach" count for October (7,696), but it does not. Is it me, the filter, or the logic?