Table with two filter criteria seems simple but...

[Anonymous]

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.

[TomMartens]

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

[Anonymous]

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

[TomMartens]

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

[Anonymous]

Sample data in PBIX..... Library Book Data

[TomMartens]

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

[Anonymous]

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

[TomMartens]

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

[Anonymous · Accepted 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