topic Comparing One measure against another measure in DAX Commands and Tips

Comparing One measure against another measure

Anonymous — Wed, 12 May 2021 10:36:18 GMT

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

Re: Comparing One measure against another measure

Jihwan_Kim — Wed, 12 May 2021 12:35:02 GMT

Hi, Anonymous

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

Active Previous & Inactive Current Week ID Count =

VAR mindatecurrentweek =

MIN ( Dates[Date] )

VAR idlistprevious =

CALCULATETABLE (

VALUES ( IDs ),

FILTER (

IDs,

IDs[Start] <= mindatecurrentweek - 1

&& IDs[End] >= mindatecurrentweek - 7

)

VAR idlistcurrent =

CALCULATETABLE (

VALUES ( IDs ),

FILTER (

IDs,

IDs[Start] <= MAX ( Dates[Date] )

&& IDs[End] >= MIN ( Dates[Date] )

)

RETURN

COUNTROWS ( EXCEPT ( idlistprevious, idlistcurrent ) )

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

Re: Comparing One measure against another measure

Anonymous — Wed, 12 May 2021 16:37:13 GMT

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!

Re: Comparing One measure against another measure

Jihwan_Kim — Wed, 12 May 2021 16:50:28 GMT

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

Re: Comparing One measure against another measure

Anonymous — Wed, 12 May 2021 21:43:48 GMT

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