cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pilot4fun Frequent Visitor
Frequent Visitor

Table with two filter criteria seems simple but...

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.

1 ACCEPTED SOLUTION

Accepted Solutions
pilot4fun Frequent Visitor
Frequent Visitor

Re: Table with two filter criteria seems simple but...

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:

 

Annotation 2019-06-22 124837.png

 

 

 

 

 

 

 

 

 

 

to this:

Annotation 2019-06-22 124837.png

 

 

 

 

 

 

 

 

 

 

Which results in this table:

 

 

Annotation 2019-06-22 124837.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

8 REPLIES 8
Super User
Super User

Re: Table with two filter criteria seems simple but...

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
pilot4fun Frequent Visitor
Frequent Visitor

Re: Table with two filter criteria seems simple but...

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

Super User
Super User

Re: Table with two filter criteria seems simple but...

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Highlighted
pilot4fun Frequent Visitor
Frequent Visitor

Re: Table with two filter criteria seems simple but...

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

Super User
Super User

Re: Table with two filter criteria seems simple but...

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:

image.png

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:

image.png

I'm not sure if you still need the inactive relationship between the tables "Dates" and "Sample_data".

 

Regards,

Tom

 

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
pilot4fun Frequent Visitor
Frequent Visitor

Re: Table with two filter criteria seems simple but...

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

 

Super User
Super User

Re: Table with two filter criteria seems simple but...

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

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
pilot4fun Frequent Visitor
Frequent Visitor

Re: Table with two filter criteria seems simple but...

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:

 

Annotation 2019-06-22 124837.png

 

 

 

 

 

 

 

 

 

 

to this:

Annotation 2019-06-22 124837.png

 

 

 

 

 

 

 

 

 

 

Which results in this table:

 

 

Annotation 2019-06-22 124837.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 1,184 guests
Please welcome our newest community members: