topic Re: Group By and Summarize in DAX Commands and Tips

Group By and Summarize

akshay2204 — Sat, 24 Jun 2023 13:19:00 GMT

Hi,

I need solution for the following .

I need a distinct count of customers who are relarted to the major group. When We ran a query in Sql we got the expected result. We are facing issue in creating the dax query for this. Please help me.

Thanks in advance

Re: Group By and Summarize

tamerj1 — Sat, 24 Jun 2023 14:35:59 GMT

Hi akshay2204

please try

NewTable =
GROUPBY (
SUMMARIZE (
FILTER ( vwSalesData, vwSalesData[YearID] = 22 && vwSalesData[MonthID] = 4 ),
vwSalesData[CustomerCode],
"MajorGroupCount", DISTINCTCOUNT ( vwSalesData[MajorGroupID] )
),
[MajorGroupCount],
"#ofCustomers", COUNTX ( CURRENTGROUP (), 1 )
)

Re: Group By and Summarize

akshay2204 — Sat, 24 Jun 2023 17:23:33 GMT

Hi tamerj1 I checked this but still throwing an error for value conversion. Basically Year ID and Month ID will be used in slicers. we only need customer count and Major group

Re: Group By and Summarize

tamerj1 — Sat, 24 Jun 2023 18:15:44 GMT

akshay2204

Calculated tables cannot interact with the filter context. This has to be a measure. In this case the year and moth slicers will filter the table automatically and no need to to be included in the dax filter.

You need to create a MajorGroupCount table. This would be a single column disconnected table that includes integers ranging from 1 to the number of unique majour groups. Can be manually inserted or created using power query or dax.
A dax exsmple would be

MajorGroupCount =

SELECTCOLUMN (
GENERATESERIES ( 1, DISTINCTCOUNT ( vwSalesData[MajorGroupID] ), 1 ),

"Count", [Value]
)

Place MajorGroupCount[Count] in a table visual along with the following measure

CountMeasure =
SUMX (
VALUES ( MajorGroupCount[Count] ),
COUNTROWS (
FILTER (
SUMMARIZE (
vwSalesData,
vwSalesData[CustomerCode],
"MajorGroupCount", DISTINCTCOUNT ( vwSalesData[MajorGroupID] )
),
[MajorGroupCount] = MajorGroupCount[Count]
)
)
)