Distinct Totals in Matrix Table from DAX Parameters as Slicers

[JSBoofy]

Hi there!

I've been struggling with a problem for a couple of weeks that PowerBI professionals in the corporate business I work in, ChatGPT and a variety of Fivver powerBI experts have been unable to crack.

I have a very specific problem at work which I've replicated as a small sized dataset on my personal computer downloadable on the google drive link below... It's a DAX problem where I've got a dynamic matrix visual with two slicers, both for multi-selecting where one is row data & the other is calculated values (using the method from The Power BI Guy on youtube: https://www.youtube.com/watch?v=WdTcj2jjk5I), built from "Modeling > Parameters > Fields". Ultimately the goal is to allow the end user to create their own views by holding ctrl+clicking the metrics they want to change the view. The SUMs work fine for Paid value, however, the totals when using DISTINCTCOUNT do not aggregate to the correct total on the row or footing the column, though every cell is correct. This causes potential issues with calculating averages on the paid / distinct Voucher_ID count when selecting numerous parameters. I'm not sure if it is a data-structure problem? Mostly I've been pointed to SUMX as a solution but there seems to be a contextual relationship problem somewhere as far as I can tell.

The parameter I created for Metrics was this DAX statement:

Metrics = {
    ("Voucher_ID", NAMEOF('Vouchers_Paid'[Voucher_ID]), 0),
    ("System", NAMEOF('Mapping - Systems'[System]), 1),
    ("Insurer", NAMEOF('Mapping - Insurers'[Insurer]), 2)
}

 

The data model looks like this:

The dataset I'm actually using has around 30 different 'metrics' in the parameters, and is around 23 million rows of data. 

ChatGPT gave me some interesting solutions, the latest one was : 

Unique Vouchers =
VAR SelectedMetric = SELECTEDVALUE('Metrics'[Metrics Fields])
RETURN
SWITCH(
SelectedMetric,
"Voucher_ID",
CALCULATE(
DISTINCTCOUNT('Vouchers_Paid'[Voucher_ID]),
ALL('Vouchers_Paid')
),
"System",
CALCULATE(
DISTINCTCOUNT('Vouchers_Paid'[Voucher_ID]),
ALL('Vouchers_Paid'),
'Vouchers_Paid'[System] = SELECTEDVALUE('Mapping - Systems'[System])
),
"Insurer",
CALCULATE(
DISTINCTCOUNT('Vouchers_Paid'[Voucher_ID]),
ALL('Vouchers_Paid'),
'Vouchers_Paid'[Insurer] = SELECTEDVALUE('Mapping - Insurers'[Insurer])
),
BLANK() // Return blank if no condition matches
)

Thanks for your time!

https://drive.google.com/file/d/15Avl3r4lJEzl5Hx-KYZjuDUR93CdSb0P/view?usp=sharing

[JSBoofy]

Further to the above, I've been trying to use other DAX statements provided by ChatGPT, for my unique calculations I have this:

Unique Vouchers = 
VAR SelectedMetric = SELECTEDVALUE(Metrics[Metrics])
RETURN
    SWITCH(
        TRUE(),
        ISINSCOPE('Vouchers_Paid'[Date]),
            COUNTX(
                VALUES('Vouchers_Paid'[Date]),
                CALCULATE(
                    DISTINCTCOUNT('Vouchers_Paid'[Voucher_ID]),
                    ALL('Vouchers_Paid')
                )
            ),
        BLANK() // Return blank if no condition matches
    )

Then upon using the performance analyzer, when I select just 1 metric from my slicer, the first DAX performed is:

// DAX Query
DEFINE
	VAR __DS0FilterTable = 
		TREATAS({"'Mapping - Systems'[System]"}, 'Metrics'[Metrics Fields])

	VAR __DS0Core = 
		CALCULATETABLE(
			SUMMARIZE(VALUES('Values'), 'Values'[Values Fields], 'Values'[Values Order], 'Values'[Values]),
			KEEPFILTERS(__DS0FilterTable)
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(101, __DS0Core, 'Values'[Values Order], 1, 'Values'[Values], 1, 'Values'[Values Fields], 1)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	'Values'[Values Order], 'Values'[Values], 'Values'[Values Fields]

Then followed by: 

// DAX Query
DEFINE
	VAR __DS0FilterTable = 
		TREATAS({"'Vouchers_Paid'[Unique Vouchers]"}, 'Values'[Values Fields])

	VAR __DS0FilterTable2 = 
		TREATAS({"'Mapping - Systems'[System]"}, 'Metrics'[Metrics Fields])

	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			ROLLUPADDISSUBTOTAL('Mapping - Systems'[System], "IsGrandTotalRowTotal"),
			ROLLUPADDISSUBTOTAL(
				'LocalDateTable_6b79853d-194b-4a82-992f-19543776ae02'[Year], "IsGrandTotalColumnTotal"
			),
			__DS0FilterTable,
			__DS0FilterTable2,
			"Unique_Vouchers", 'Vouchers_Paid'[Unique Vouchers]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			102,
			SUMMARIZE(__DS0Core, 'Mapping - Systems'[System], [IsGrandTotalRowTotal]),
			[IsGrandTotalRowTotal],
			0,
			'Mapping - Systems'[System],
			1
		)

	VAR __DS0SecondaryBase = 
		SUMMARIZE(
			__DS0Core,
			'LocalDateTable_6b79853d-194b-4a82-992f-19543776ae02'[Year],
			[IsGrandTotalColumnTotal]
		)

	VAR __DS0Secondary = 
		TOPN(
			102,
			__DS0SecondaryBase,
			[IsGrandTotalColumnTotal],
			1,
			'LocalDateTable_6b79853d-194b-4a82-992f-19543776ae02'[Year],
			1
		)

	VAR __DS0BodyLimited = 
		NATURALLEFTOUTERJOIN(
			__DS0PrimaryWindowed,
			SUBSTITUTEWITHINDEX(
				__DS0Core,
				"ColumnIndex",
				__DS0Secondary,
				[IsGrandTotalColumnTotal],
				ASC,
				'LocalDateTable_6b79853d-194b-4a82-992f-19543776ae02'[Year],
				ASC
			)
		)

EVALUATE
	__DS0Secondary

ORDER BY
	[IsGrandTotalColumnTotal],
	'LocalDateTable_6b79853d-194b-4a82-992f-19543776ae02'[Year]

EVALUATE
	__DS0BodyLimited

ORDER BY
	[IsGrandTotalRowTotal] DESC, 'Mapping - Systems'[System], [ColumnIndex]


// DAX Query
DEFINE
	VAR __DS0FilterTable = 
		TREATAS({"'Mapping - Systems'[System]"}, 'Metrics'[Metrics Fields])

	VAR __DS0Core = 
		CALCULATETABLE(
			SUMMARIZE('Metrics', 'Metrics'[Metrics Fields], 'Metrics'[Metrics Order], 'Metrics'[Metrics]),
			KEEPFILTERS(__DS0FilterTable)
		)

	VAR __DS0BodyLimited = 
		TOPN(
			152,
			__DS0Core,
			'Metrics'[Metrics Order],
			1,
			'Metrics'[Metrics Fields],
			1,
			'Metrics'[Metrics],
			1
		)

EVALUATE
	__DS0BodyLimited

ORDER BY
	'Metrics'[Metrics Order], 'Metrics'[Metrics Fields], 'Metrics'[Metrics]

Even with all this, I'm not sure what I need to do to have it return the correct grand totals for just 1 metric then furthermore how to make sure sub-totals are correct when selecting multiple metrics to add into the row data. Hopefully someone can help!

[ValtteriN]

Hi,

The issue is likely more simple than it seems. Power BI matrix totals work in a bit non-intuitive way. The visual calculations released in this months update make this easier to solve, but since they are in preview. Here is one way to solve this:

Example data (due to security reasons I don't dowload files from drive sorry for that):

With simple distinctcount this is the result:

a = CALCULATE(

DISTINCTCOUNT('Table (23)'[ID]),

ALL('Table (23)'),

'Table (23)'[System] = SELECTEDVALUE('Table (23)'[System])

)


Now by using X functions here is the result:

a =

COUNTX('Table (23)',

[ID]

)

Since COUNTX is evaluated over the table the COUNT is correct. This is due to the fact that on total row functions like SELECTEDVALUE are evaluated in "empty" filter context. COUNTX forces the evaluation to work in a different way and this way the totals make sense. So as a solution try using similar stucture. Ping me if you have questions.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[JSBoofy]

Hey ValtteriN, 

Thanks for your speedy response! I tried adding in your CountX but I'm actually looking for the distinct count total as the following DAX presents me with the same as a normal volume count;

Unique Vouchers = 
COUNTX(Vouchers_Paid,Vouchers_Paid[Voucher_ID])

After looking up visual calculations (noted that it's in preview), I don't think I'm able to apply an additional calculation to a matrix table as it's not an actual visual.

The goal is to be able to select multiple metrics so the above screenshot would look like this but with distinct counts sub and grand totals appearing correctly.

In the above, when pivoted using excel it should look like this;

I can appreciate your security concerns - perhaps there's another file-sharing site you would prefer I use for virus scanning or something if you'd like mockup sample data I've created to try to solve this problem?

Again - really appreciate your time and help!