cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eoingrosch
Frequent Visitor

Complex DAX Measure incorrect with Date Filter Context

I'm having trouble with a faily complex Measure when paired with a Date filter context. The use case has to do with Billing, and a Company (aka "Customer") Retention Status being "Landed" when they are first billed, "Churned" if billing stops, and then "Reactivated" if they are billed again in the future.

 

Raw Data:

Capture.PNG

 

Below is the desired output. The highlighted "Reactivation_Amount" is the issue.

Capture1.PNG

 

Below is my current output. Notice that the $537.50 is incorrectly classified as Landed. However, when [Date] is not in the row context (Company level), this amount shows up under "Reactivation." 

Capture2.PNG

 

The DAX for the Measures is complex because the classification of Landed/Reactivated/Churned is evaluated at the Company-Date level, although reporting (row context) may be at different levels. The only difference between [Landed_Amount] and [Reactivation_Amount] is the bolded section ... "Landed" records are those where the DateKey = MIN([DateKey]) for that Company, and "Reactivated" records are those where DateKey <> MIN([DateKey])

 

 

Landed_Amount:=
	VAR FilteredTable = 
		FILTER(Fact_SaaS_Billing
			,SUMX(	FILTER(Fact_SaaS_Billing
						,[Dim_Company_Key]=EARLIER([Dim_Company_Key]) 
						&& [DateKey]=EARLIER([DateKey])
					), [Cycle_Amount])>0 
		&&	SUMX(	FILTER(Fact_SaaS_Billing
						,[Dim_Company_Key]=EARLIER([Dim_Company_Key]) 
						&& [DateKey]=EARLIER([DateKey])
					), [Prior_Cycle_Amount])<=0
		&&	MINX(	FILTER(Fact_SaaS_Billing
						,[Dim_Company_Key]=EARLIER([Dim_Company_Key])
					), [DateKey]) = [DateKey]
		)
	RETURN
		SUMX(FilteredTable,[Cycle_Amount]*[Exchange_Rate])

VAR [FilteredTable] Result for "Landed" is correct:

Capture3.PNG

 

 

 

Reactivation_Amount:=
	VAR FilteredTable = 
		FILTER(Fact_SaaS_Billing
			,SUMX(	FILTER(Fact_SaaS_Billing
						,[Dim_Company_Key]=EARLIER([Dim_Company_Key]) 
						&& [DateKey]=EARLIER([DateKey])
					), [Cycle_Amount])>0 
		&&	SUMX(	FILTER(Fact_SaaS_Billing
						,[Dim_Company_Key]=EARLIER([Dim_Company_Key]) 
						&& [DateKey]=EARLIER([DateKey])
					), [Prior_Cycle_Amount])<=0
		&&	MINX(	FILTER(Fact_SaaS_Billing
						,[Dim_Company_Key]=EARLIER([Dim_Company_Key])
					), [DateKey]) <> [DateKey]
		)
	RETURN
		SUMX(FilteredTable,[Cycle_Amount]*[Exchange_Rate])

VAR [FilteredTable] Result for "Reactivation" is correct:

Capture4.PNG

 

Question: Any advice on how I can correct this issue so that the "Reactivation_Amount" gets classified correctly when reporting at the [Date] level?

 

Thank you!

 

4 REPLIES 4
Microsoft v-yulgu-msft
Microsoft

Re: Complex DAX Measure incorrect with Date Row Context

Hi @eoingrosch,

 

Please try this:

Landed_Amount:=
	VAR FilteredTable = 
		FILTER(Fact_SaaS_Billing
			,SUMX(	FILTER(Fact_SaaS_Billing
						,[Dim_Company_Key]=EARLIER([Dim_Company_Key]) 
						&& [DateKey]=EARLIER([DateKey])
					), [Cycle_Amount])>0 
		&&	SUMX(	FILTER(Fact_SaaS_Billing
						,[Dim_Company_Key]=EARLIER([Dim_Company_Key]) 
						&& [DateKey]=EARLIER([DateKey])
					), [Prior_Cycle_Amount])<=0
		&&	MINX(	FILTER(ALLSELECTED(Fact_SaaS_Billing)
						,[Dim_Company_Key]=EARLIER([Dim_Company_Key])
					), [DateKey]) = [DateKey]
		)
	RETURN
		SUMX(FilteredTable,[Cycle_Amount]*[Exchange_Rate])

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
eoingrosch
Frequent Visitor

Re: Complex DAX Measure incorrect with Date Row Context

Hi @v-yulgu-msft, thanks for the response and idea. I've added ALLSELECTED() as you described, but no change to the result of "Landed" and "Reactivation" unfortunately. 

 

Any other tricks you can think of that might work? Thanks!

Microsoft v-yulgu-msft
Microsoft

Re: Complex DAX Measure incorrect with Date Row Context

Hi @eoingrosch,

 

How about using ALL () instead?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
eoingrosch
Frequent Visitor

Re: Complex DAX Measure incorrect with Date Filter Context

Hi @v-yulgu-msft and all,

 

I've been racking my brain and trying different options to solve this Measure over the past few days, but still no luck. I think ALL() and ALLSELECTED() are on the right path of what's needed for the MinDate part of this Measure. It needs to take into account all the filter contexts applied *except* Date. ALL() removes all filter contexts which goes too far, and ALLSELECTED() is still affected by Date, so not enough. I can see that ALLSELECTED() is a complex function that can be used in different ways (on table or column), and it could possibly be used to solve this issue, but unclear on how to do it.

 

I've posted a sample of the data at the below link, which includes the example I used in this post:

 

https://drive.google.com/open?id=1Hsu2eLaSp7NwuH6YYdsdLuj3ZuT9stlP

 

Thank you!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors