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