topic Re: Inverse filtering across tables within a date range in DAX Commands and Tips

Inverse filtering across tables within a date range

MHO-3XN — Tue, 05 Dec 2023 14:21:44 GMT

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:

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:

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!

Re: Inverse filtering across tables within a date range

sjoerdvn — Tue, 05 Dec 2023 15:16:34 GMT

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

Re: Inverse filtering across tables within a date range

MHO-3XN — Tue, 05 Dec 2023 15:52:59 GMT

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

Re: Inverse filtering across tables within a date range

sjoerdvn — Tue, 05 Dec 2023 16:48:35 GMT

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....

Re: Inverse filtering across tables within a date range

MHO-3XN — Wed, 06 Dec 2023 09:05:57 GMT

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