Forum Discussion

akshay2204's avatar
akshay2204
Frequent Visitor
3 years ago

Group By and Summarize

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...
akshay2204's avatar
akshay2204
Frequent Visitor
3 years ago

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

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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