topic Re: DAX Summarizecolumns and then countrows per group in DAX Commands and Tips

DAX Summarizecolumns and then countrows per group

mikeys — Tue, 16 Jan 2024 14:43:13 GMT

Hi,

I have query with 3 dimensions (Continent/CountryRegion/City) part of paginated report.

I would like to highlight only those child groups where there is 1 item.

So I need to get a count of items per each parent group.

In this case, let's write the query, to be more exact.

EVALUATE
VAR __t1 =
SUMMARIZECOLUMNS (
Customer[Continent],
Customer[CountryRegion],
Customer[City],
"Amount", [Sales Amount]
)

RETURN
GROUPBY(
__t1,
Customer[Continent],
Customer[CountryRegion],
"Count" , countX(CURRENTGROUP(), Customer[CountryRegion] )
) ORDER BY Customer[Continent] DESC

Which brings me this

Result I am looking for is

And in a case where I have 3 dimensions
EVALUATE
VAR __t1 =
SUMMARIZECOLUMNS (
Customer[Continent],
Customer[CountryRegion],
Customer[City],
"Amount", [Sales Amount]
)

RETURN
GROUPBY(
__t1,
Customer[Continent],
Customer[CountryRegion],
Customer[City],
"Count" , countX(CURRENTGROUP(), Customer[City] )
) ORDER BY Customer[Continent] DESC

I get

But I would like to see

How should I approach this?
Thank you!

Re: DAX Summarizecolumns and then countrows per group

123abc — Wed, 17 Jan 2024 08:18:02 GMT

To achieve the desired result, you can use the following approach:

EVALUATE
VAR __t1 =
SUMMARIZECOLUMNS (
Customer[Continent],
Customer[CountryRegion],
Customer[City],
"Amount", [Sales Amount]
)

VAR __t2 =
SUMMARIZECOLUMNS (
__t1,
Customer[Continent],
Customer[CountryRegion],
"Count", COUNTROWS(__t1)
)

RETURN
GROUPBY(
__t2,
Customer[Continent],
Customer[CountryRegion],
"Count", COUNTROWS(CURRENTGROUP())
) ORDER BY Customer[Continent] DESC, Customer[CountryRegion] DESC

In this query:

The first SUMMARIZECOLUMNS creates a table (__t1) with the necessary dimensions and the "Amount" measure.
The second SUMMARIZECOLUMNS adds another level of summarization (__t2) based on the first table (__t1), and calculates the count of rows for each combination of Continent and CountryRegion.
The final GROUPBY groups the second table (__t2) by Continent and CountryRegion and calculates the count of rows for each group.

This should give you the desired result, showing the count of items per each parent group, and only highlighting those groups where there is 1 item.

Re: DAX Summarizecolumns and then countrows per group

Mikeysw — Wed, 17 Jan 2024 09:21:34 GMT

Thank you for your answer!

I've tried your query but it gives me an error