Forum Discussion
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:
Below is the desired output. The highlighted "Reactivation_Amount" is the issue.
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."
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:
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:
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
- v-yulgu-msftMicrosoft Employee
Hi Anonymous,
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
- AnonymousNot applicable
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!
- v-yulgu-msftMicrosoft Employee
Hi Anonymous,
How about using ALL () instead?
Regards,
Yuliana Gu