The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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
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]
)
)
)
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 )
)
User | Count |
---|---|
11 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
23 | |
14 | |
13 | |
10 | |
8 |