Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello there,
For the below ID, start & end dates, there are 2 measures in place to get the active IDs until week in context & previous week in context - based on Start & end dates.
What is the best way to compare the IDs resulted by the 2 measures to arrive at the third measure which identifies the IDs active in previous week context but not in current week context?
ID | Start | End | |
A | 01/01/2021 | 02/01/2021 | |
B | 01/01/2021 | 06/01/2021 | |
C | 06/01/2021 | 07/01/2021 | |
A | 11/01/2021 | 12/01/2021 | |
Date | |||
Date | Week | Day | |
01/01/2021 | 2021-1 | Fri | |
02/01/2021 | 2021-1 | Sat | |
03/01/2021 | 2021-1 | Sun | |
04/01/2021 | 2021-2 | Mon | |
05/01/2021 | 2021-2 | Tue | |
06/01/2021 | 2021-2 | Wed | |
07/01/2021 | 2021-2 | Thu | |
08/01/2021 | 2021-2 | Fri | |
09/01/2021 | 2021-2 | Sat | |
10/01/2021 | 2021-2 | Sun | |
11/01/2021 | 2021-3 | Mon | |
Result by Week | |||
Week | Measure 1 - Active ID count until the week | Measure 2 - Active ID count until previous week | Measure 3 - Active in previous but inactive in current week |
2021-1 | 2 | ||
2021-2 | 2 | 2 | |
2021-3 | 1 | 2 |
Appreciate any suggestion.
Regards,
Pravallika
Thanks again for taking time to respond.
I do not want to use exclude & rather use my two measures to work in a way to find out the inactive ones. This is because, the result using exclude cannot be sliced or diced with other filters.
This is the reason why I used the title of comparing one measure against another.
Is there any other way you can think of?
Cheers,
Pravallika
Hello Jihwan,
Thanks for responding, I tried using EXCEPT() earlier too. There is an active relationship between the tables Date & IDs on Start_Date.
Even with your example, when you add a relationship the measure doesn't calculate.
Is there any other workaround you can think of?
Thanks!
Hi, @Anonymous
Thank you for your feedback.
In my case, if there are more than two date-related columns in a fact table, I generally create inactive relationships and use the USERELATIONSHIP function later, if I want to filter by the dim-date table. Or, I generally do not create relationships and use TREATAS function later, if I want to filter by the dim-date table.
If, in your case, you have an active relationship with startdate, then you can simply remove the filter inside the measure. By using ALL can help to remove the filter.
I have amended a relationship, same as your suggestion, and also amended all measures in the sample pbix file's link down below. Please check.
https://www.dropbox.com/s/lm65er2k95xe4d1/pravallikasri.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @Anonymous
Please check the below picture and the sample pbix file's link down below.
All measures are in the sample pbix file.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
56 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |