Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MHO-3XN
Regular Visitor

Inverse filtering across tables within a date range

Hi all, I'm struggling to do inverse filtering across tables within a given date range.

I have a table of users recorded on a given date and I have a table of all users, like this:

MHO3XN_2-1701785747714.png


I want to have a table that shows all the users NOT recorded in a given date range. You can see in the file I posed below, but if I select Oct-29 to Nov-10, I want my table to show all the users who are in the all users table and who are not in the users recorded table within that date range. I want the date range to be controlled via a filter on the powerBI report page like this:

MHO3XN_3-1701785927584.png


I managed some simple DAX to create a table that shows the users that never appear in the users recorded table (it's called unused_users in my pbi)

unused_users = EXCEPT(AllUsers, SUMMARIZE(UserDate, UserDate[user]))

but it of course ignores the date. How can I create a table that will update properly when the date range is selected on the report page?

Here is my excel and pbi file:
https://we.tl/t-F5WF2EVYVY

Thank you in advance!

1 ACCEPTED SOLUTION
sjoerdvn
Super User
Super User

First of all, get rid of the unused_users table. Computed tables are calulated at import, so never take into account any slicers or filters on the report.
To see the users not in the selected period:
create a measure like this:

# recorded users = COUNTROWS(UserDate)
 
Now create a visual and add the user column from AllUsers, and then add a filter on the visual [# recorded users] is blank
sjoerdvn_1-1701789382222.png

 


 








View solution in original post

4 REPLIES 4
sjoerdvn
Super User
Super User

First of all, get rid of the unused_users table. Computed tables are calulated at import, so never take into account any slicers or filters on the report.
To see the users not in the selected period:
create a measure like this:

# recorded users = COUNTROWS(UserDate)
 
Now create a visual and add the user column from AllUsers, and then add a filter on the visual [# recorded users] is blank
sjoerdvn_1-1701789382222.png

 


 








Ah ok I did not know that calculated tables do not take filters into account. Your solution works well with the file I uploaded - thank you for that!! - but in my working file the solution doesn't work. I'm worried it is because the UserDate table is also a calcualted table in my real file.

I've created another version of my file that more closely resembes my real data. In it, I renamed the table I faked to FAKE_UserDate, and I added two new tables, raw_dailyusersoftware which contains all the raw data and the ProductSoftware table. The calcualted table (that FAKE_UserDate was based on) is called calculated_UserDate and the DAX expression is:

 

calculated_UserDate = CALCULATETABLE(  SUMMARIZE(raw_dailyusersoftware, raw_dailyusersoftware[user], raw_dailyusersoftware[date]), FILTER( ProductSoftware, ProductSoftware[Software_Product]= "DD All Apps") ) 

 


Could you tell me how to get my desired result on the raw data table and bypass the calculated table entirely?

Here is the updated file:
https://we.tl/t-kpcroD4Y8D 

The problem is not in Userdate being a calculated table. You should be checking you data and the relationships between the tables. You need a relationship between allusers and your userdate table (calculated and/or fake). I see other relationships that should not be there (with expectedresult) and the calculated_userdate table has users that are not in allUsers....

You're right, in my real file I had missed one relationship, then your measure worked again. Thank you!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.