Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
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:
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:
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!
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
7 |