Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am hoping someone may know of a performant solution to my problem.
Sample data:
dim_user
user_id |
1 |
fact_user_types
user_id | type | startdate | enddate | status |
1 | A | 2/1/2022 | 1/1/2100 | On Hold |
1 | B | 3/2/2022 | 3/5/2022 | Active |
1 | C | 3/5/2022 | 1/1/2100 | Active |
Filters:
We are looking for the following:
Here's the kicker...
If a user_id has an On Hold record AND an Active record in the same date range, we need to exclude them from the On Hold count. A user should never counted in both the Active and On Hold counts. Active will always trump On Hold.
Using the above example, these are the expected results for different scenarios:
Scenario 1:
Date Range 3/1/2022 - 3/31/2022, All Types
Active = 1
On Hold = 0 *
*even though the user has an on hold record, the fact that they also have an active record eliminates them from this count
Scenario 2:
Date Range 3/1/2022 - 3/31/2022, Type A Only
Active = 0
On Hold = 1
What I have so far are 2 measures for isActive and isOnHold. This gives me a value for each record.
isOnHold is the same but with "On Hold"
So, when the Date Range is 3/1/2022 - 3/3/2022, I see this:
id | type | startdate | enddate | status | isActive | isOnHold |
1 | A | 2/1/2022 | 1/1/2100 | On Hold | 0 | 1 |
1 | B | 3/2/2022 | 3/5/2022 | Active | 1 | 0 |
1 | C | 3/5/2022 | 1/1/2100 | Active | 0 | 0 |
What I'd like is 1 record per user_id:
id | status |
1 | Active |
and 2 cards:
Thank you in advance for any assistance! 🙂 Joy
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
This is great! Thank you so much. I see how you added a column = {Number.From([startdate])..Number.From([enddate])} in the fact table. I'm a little concerned that it will make for a very very large sized table, but maybe it's better to store that data in memory rather than calculate the to/from on the report. I will experiment with this approach. Thanks again!!
You are welcome.
Oops - I c/p the actual dax for isActive instead of the simplified version with table/columns from my sample:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |