Calculate values at different granular in DAX meas...

whjqb · ‎11-13-2024

I am struggling on creating a DAX measure to solve below problem.

I would like to filter on below dataset where dim_reporting_date_key is the latest date for each dim_case_key with a quarter, then counting how many cases at "Age Over Time" level.

I only show partially data here as the full dataset is big.

The script should like below if I use SQL.

select [Age Over Time],
       count(distinct dim_case_key) as result
from
(
    select dim_case_key,
           dim_reporting_date_key,
           [Case Status],
           [Open Days],
           [Age Over Time],
           max(dim_reporting_date_key) OVER (PARTITION BY dim_case_key) as LatestDate
    from #temp2
) x
where dim_reporting_date_key = [LatestDate]
group by [Age Over Time]

Here is my dax measure.

// DAX Query
DEFINE
	VAR __Filter =
	TREATAS(
		{
			20240731,
			20240831,
			20240930
		},
		'Fact Case Monthly Snapshot'[dim_reporting_date_key]
	)

	MEASURE 'Fact Case Monthly Snapshot'[LDate] = (
		CALCULATE(
			MAX('Fact Case Monthly Snapshot'[dim_reporting_date_key]),
			ALLSELECTED(
				'Fact Case Monthly Snapshot'[dim_reporting_date_key],
				'Fact Case Monthly Snapshot'[Age Over Time]
			)
		)
		)
	MEASURE 'Fact Case Monthly Snapshot'[Result] =

		VAR latestDate =
		CALCULATE(
			MAX('Fact Case Monthly Snapshot'[dim_reporting_date_key]),
			ALLSELECTED(
				'Fact Case Monthly Snapshot'[dim_reporting_date_key],
				'Fact Case Monthly Snapshot'[Age Over Time]
			)
		)
		VAR latestValue =
		CALCULATE(
			DISTINCTCOUNT('Fact Case Monthly Snapshot'[dim_case_key]),
			FILTER(
				'Fact Case Monthly Snapshot',
				'Fact Case Monthly Snapshot'[dim_reporting_date_key] = latestDate
			)
		)

		RETURN
			SUMX(
				SUMMARIZE(
					'Fact Case Monthly Snapshot',
					'Fact Case Monthly Snapshot'[dim_case_key],
					"LatestValue", latestValue
				),
				[LatestValue]
			)
			


	VAR __DS0Core =
	SUMMARIZECOLUMNS(
		'Fact Case Monthly Snapshot'[dim_case_key],
		'Fact Case Monthly Snapshot'[dim_reporting_date_key],
		'Fact Case Monthly Snapshot'[Age Over Time],
		__Filter,
		"Result", 'Fact Case Monthly Snapshot'[Result],
		"Latest Date", [LDate]
	)

	VAR __DS0Core2 =
	SUMMARIZECOLUMNS(
		'Fact Case Monthly Snapshot'[Age Over Time],
		__Filter,
		"Result", 'Fact Case Monthly Snapshot'[Result],
		"Latest Date", [LDate]
	)

EVALUATE

	// __DS0Core
	// ORDER BY
	// 	'Fact Case Monthly Snapshot'[dim_case_key],
	// 	'Fact Case Monthly Snapshot'[dim_reporting_date_key] DESC


	__DS0Core2

The result is incorrect. The value of result is too high.

If I run the measure at case granular and the result looks good.

// DAX Query
DEFINE
	VAR __Filter =
	TREATAS(
		{
			20240731,
			20240831,
			20240930
		},
		'Fact Case Monthly Snapshot'[dim_reporting_date_key]
	)

	MEASURE 'Fact Case Monthly Snapshot'[LDate] = (
		CALCULATE(
			MAX('Fact Case Monthly Snapshot'[dim_reporting_date_key]),
			ALLSELECTED(
				'Fact Case Monthly Snapshot'[dim_reporting_date_key],
				'Fact Case Monthly Snapshot'[Age Over Time]
			)
		)
		)
	MEASURE 'Fact Case Monthly Snapshot'[Result] =

		VAR latestDate =
		CALCULATE(
			MAX('Fact Case Monthly Snapshot'[dim_reporting_date_key]),
			ALLSELECTED(
				'Fact Case Monthly Snapshot'[dim_reporting_date_key],
				'Fact Case Monthly Snapshot'[Age Over Time]
			)
		)
		VAR latestValue =
		CALCULATE(
			DISTINCTCOUNT('Fact Case Monthly Snapshot'[dim_case_key]),
			FILTER(
				'Fact Case Monthly Snapshot',
				'Fact Case Monthly Snapshot'[dim_reporting_date_key] = latestDate
			)
		)

		RETURN
			SUMX(
				SUMMARIZE(
					'Fact Case Monthly Snapshot',
					'Fact Case Monthly Snapshot'[dim_case_key],
					"LatestValue", latestValue
				),
				[LatestValue]
			)
			


	VAR __DS0Core =
	SUMMARIZECOLUMNS(
		'Fact Case Monthly Snapshot'[dim_case_key],
		'Fact Case Monthly Snapshot'[dim_reporting_date_key],
		'Fact Case Monthly Snapshot'[Age Over Time],
		__Filter,
		"Result", 'Fact Case Monthly Snapshot'[Result],
		"Latest Date", [LDate]
	)

	VAR __DS0Core2 =
	SUMMARIZECOLUMNS(
		'Fact Case Monthly Snapshot'[Age Over Time],
		__Filter,
		"Result", 'Fact Case Monthly Snapshot'[Result],
		"Latest Date", [LDate]
	)

EVALUATE

	__DS0Core
	ORDER BY
	'Fact Case Monthly Snapshot'[dim_case_key],
	'Fact Case Monthly Snapshot'[dim_reporting_date_key] DESC


	// __DS0Core2

The result shows 1 for the correct records and blank for others.

I am not sure where is wrong of my measure and why the result is aggregated incorrectly. Can someone help me to solve this issue please?

Thanks a lot

v-xuxinyi-msft · ‎11-14-2024

Hi @whjqb

Thanks for the reply from Poojara_D12 .

I have created the following measure to calculate LatestDate,

LatestDate = CALCULATE(MAX([dim_reporting_date_key]), 'Fact Case Monthly Snapshot'[dim_case_key] = MAX([dim_case_key]))

but I have some questions about the result of count. What is the calculation logic of count? Is it to get the maximum date of each Age Over time, and if there is a maximum date of dim_case_key that is the same as it, then count it? Could you please give the expected result based on the sample data you provided so that we can better help you?

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

whjqb · ‎11-14-2024

Hi Yulia,

Thanks for your help. Here is more explanation.

"max_date" is the maximum dim_reporting_date_key for each dim_case_key. "Selected" is Y if dim_reporting_date_key = max_date. I would like to select those "Y" records and ignore others.

Then I would like to aggregate to Age Over Time level and count distinct number of dim_case_key. So the expected result should be like this.

Thanks

Ming

v-xuxinyi-msft · ‎11-27-2024

Hi @whjqb

I would like to apologize for the belated reply.

Please try the following measures:

max_date = CALCULATE(MAX([dim_reporting_date_key]), ALLEXCEPT('Table', 'Table'[dim_case_key]))

Selected = IF(MAX([dim_reporting_date_key]) = [max_date], "Y", "NULL")

result = CALCULATE(DISTINCTCOUNT('Table'[dim_case_key]), FILTER(ALLEXCEPT('Table', 'Table'[Age Over Time]), [Selected] = "Y"))

Output:

But the result I get is different from yours, according to the screenshot you provided, the result of "60-90" should be 7, not 11. But I think it's because the sample data is incomplete. If you have any questions, please feel free to let me know.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

whjqb · ‎11-27-2024

Hi Yulia,

Thanks for your response. However, your solution does not work for me.

I also need to add slicer for dim_reporting_date_key to let user dynamic select months. If I select July and Aug, then the max date should be 20240831 for dim_case_key 101388. I tried to add keepfilters in the measures but it is still giving me wrong number.

Poojara_D12 · ‎11-13-2024

Hi @whjqb

It seems the issue you're facing is due to an incorrect aggregation, which results in higher values than expected. This can happen if the calculation isn’t properly limited to the latest date per dim_case_key. In your case, the key is to ensure that you’re only counting each dim_case_key once at its latest reporting date.

Here’s an approach to modify your DAX to make sure you only count each case on the latest reporting date:

Solution

Identify the Latest Date Per Case: Create a variable to capture the latest date per dim_case_key.
Filter for Latest Date: Use this variable in a filter to make sure only cases with their latest date are counted.
Count Distinct Cases: Finally, count the distinct cases based on the filtered table.

Here’s how this DAX measure could look:

Result = 
VAR LatestDatePerCase =
    CALCULATE(
        MAX('Fact Case Monthly Snapshot'[dim_reporting_date_key]),
        ALLEXCEPT('Fact Case Monthly Snapshot', 'Fact Case Monthly Snapshot'[dim_case_key])
    )

RETURN
    CALCULATE(
        DISTINCTCOUNT('Fact Case Monthly Snapshot'[dim_case_key]),
        'Fact Case Monthly Snapshot'[dim_reporting_date_key] = LatestDatePerCase,
        TREATAS(
            {20240731, 20240831, 20240930},
            'Fact Case Monthly Snapshot'[dim_reporting_date_key]
        )
    )

Explanation

LatestDatePerCase: This variable calculates the latest dim_reporting_date_key for each dim_case_key, ignoring other filters.
DISTINCTCOUNT with Filter: We then use DISTINCTCOUNT to count distinct cases only where dim_reporting_date_key matches LatestDatePerCase for each dim_case_key. The TREATAS function is used to apply the filter to include only relevant dates.

Additional Tips

Avoid Nested Aggregation Issues: Summing up DISTINCTCOUNT results can sometimes lead to inflated counts, so the approach here directly applies the date filter within the calculation.
Debugging with Intermediate Tables: If you're still having trouble, try using SUMMARIZE or ADDCOLUMNS to create intermediate tables for debugging and verify the date filtering step-by-step.

This should ensure that your measure correctly counts each dim_case_key only once on its latest date. Let me know if this provides the correct result or if further adjustments are needed!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

whjqb · ‎11-14-2024

Hi Poojara,

Thanks for your help.

I also want to aggregate the filtered dataset to Age Over Time level and count the distinct numbers of dim_case_key. More explanations are in my reply to @v-xuxinyi-msft .

Thanks

Ming