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 creating the dax query for this. Please help me. 

 

Thanks in advance

 

3 Replies

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

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

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