Forum Discussion

sbuster's avatar
sbuster
Icon for Helper I rankHelper I
3 years ago
Solved

DAX remove duplicates or groupby

Hello, I have the follow dataset that has been denormalized (columns A-D).  The result I'm looking for is in column G/H.  I simply wanto a count of the Type field grouped by AnalyticID.  As you can see in the data, a given AnalyticID will have multiple rows because of the Input column but for the most part AnalyticID and Type will be one-to-one, so if I could remove duplicates based on AnalyticID+Type, and then groupby it would work, but I can't quite figure out how to do that.

 

Any help would be appreciated.

Tips and Tricks
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    Hi sbuster 

     

    Please try the following:

     

    Data in Power BI (Table Name = SampleTable)

     

    Measure 

    DistinctCount Analytical ID = DISTINCTCOUNT(SampleTable[AnalyticID])

     

    Result after putting int visual:

     

    Best regards
    Michael
    -----------------------------------------------------
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    Appreciate your thumbs up!
    @ me in replies or I'll lose your thread.

     

     

     

     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a new table.

     

     

     

    New table =
GROUPBY (
    SUMMARIZE ( Data, Data[AnalyticID], Data[Type] ),
    Data[Type],
    "@Count", SUMX ( CURRENTGROUP (), 1 )
)

     

3 Replies

  • Mikelytics's avatar
    Mikelytics
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Hi sbuster 

     

    Please try the following:

     

    Data in Power BI (Table Name = SampleTable)

     

    Measure 

    DistinctCount Analytical ID = DISTINCTCOUNT(SampleTable[AnalyticID])

     

    Result after putting int visual:

     

    Best regards
    Michael
    -----------------------------------------------------
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    Appreciate your thumbs up!
    @ me in replies or I'll lose your thread.

     

     

     

     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a new table.

     

     

     

    New table =
GROUPBY (
    SUMMARIZE ( Data, Data[AnalyticID], Data[Type] ),
    Data[Type],
    "@Count", SUMX ( CURRENTGROUP (), 1 )
)

     

    • sbuster's avatar
      sbuster
      Icon for Helper I rankHelper I
      3 years ago

      That seems to do the trick.. both responses worked but I accepted this as the solution as I was looking to do this in dax.