Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
JSBoofy
New Member

Distinct Totals in Matrix Table from DAX Parameters as Slicers

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)
}

 

PowerBI.png 

The data model looks like this:

PowerBI2.png

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

3 REPLIES 3
JSBoofy
New Member

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
Super User
Super User

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):

ValtteriN_0-1708342250100.png

 

With simple distinctcount this is the result:

a = CALCULATE(
DISTINCTCOUNT('Table (23)'[ID]),
ALL('Table (23)'),
'Table (23)'[System] = SELECTEDVALUE('Table (23)'[System])
)


ValtteriN_1-1708342320433.png

 

Now by using X functions here is the result:

a =
COUNTX('Table (23)',
[ID]
)
ValtteriN_2-1708342528250.png

 

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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])

PowerBI3.png

 

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.

PowerBI4.png

 

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

EXCEL1.png

 

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!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors