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 am trying to establish two different date filtering criteria across one table, different columns that iterate by date. Following is the problem defined in terms of library books checked out.
This is the source data I start with:
Data Source Table
ID | Borrow Date | Return Date |
HIST0010 | 15-Jun-2016 | 04-Jul-2016 |
FICT2720 | 12-Jan-2017 |
|
BIOG2240 | 23-Aug-2016 | 29-Sep-2016 |
… |
|
|
I have a Date table with a defined relationships 1-to-many Date to Borrow Date (active) and 1-to-many Date to Return Date (inactive).
Lookup Table Dates
Date |
01-Jan-2016 |
02-Jan-2016 |
03-Jan-2016 |
… |
Target Table should look something like ths:
Month | Number of books checked out anytime and not yet returned at the end of each month | Average duration in days of checkout of all books not yet returned at the end of each month |
January, 2016 | 340 | 15 |
February, 2016 | 260 | 12 |
March, 2016 | 554 | 23 |
… |
|
|
Conceptually (and dysfunctionally) this is as follows:
Still_Out = COUNTROWS( FILTER( books, ( books[checked_out] < [Last_day_this_month] && books[returned] > [Last_day_this_month] ) ) )
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.
Many thanks!
P.S. It seems likely that a similar scenario has been covered elsewhere, so happy to simply have a pointer to a discussion.
Solved! Go to Solution.
Tom,
Thank you for your patient help. I have solved my problem in a very simple way. The original data and the original query are unchanged. I have simply changed the relationships from this:
to this:
Which results in this table:
So: problem solved. I must give a nod to Reza Rad in the RADACAD blog for his posting, "Scenarios of Using Calculated Tables in Power BI" which pointed me in the right direction.
See Calculated Tables
Hey,
not sure but maybe it is as simple as activating the inactive relationship using the DAX function USERELATIONSHIP: https://dax.guide/userelationship/
The DAX will be similar to this:
CALCULATE( <expression> ,USERELATIONSHIP(...) ,<additional filter if necessary> )
Hopefully this provides you with some ideas!
Regards;
Tom
Hi Tom,
Thanks for the quick reply!
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.
Cheers,
-- Greg
Hey,
can you please provide a pbix that contains sample data and reflects your model, upload the file to onedrive or dropbox and share the link.
Regards,
Tom
Hey,
thanks for providing the sample data.
I choose a slightly different approach, I created a new table using this DAX statement:
not present = var maxDate = MAX('Dates'[Date]) return GENERATE( 'Sample_Data' , var returndate = [Return Date] var enddate = IF(ISBLANK(returndate) , maxDate , returndate) return 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
Books = DISTINCT( 'Sample_Data'[ID] )
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] return CALCULATE( COUNTROWS( FILTER( 'not present' , [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".
Regards,
Tom
Hi Tom,
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?
Thanks for your patience!
-- Greg
Hey Greg,
please provide the pbix you created as it is difficult to understand what you did, I will have a closer look during this week.
I also did not understand what you expected by setting the date filters.
Regards,
Tom
Tom,
Thank you for your patient help. I have solved my problem in a very simple way. The original data and the original query are unchanged. I have simply changed the relationships from this:
to this:
Which results in this table:
So: problem solved. I must give a nod to Reza Rad in the RADACAD blog for his posting, "Scenarios of Using Calculated Tables in Power BI" which pointed me in the right direction.
See Calculated Tables
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |