DAX remove duplicates or groupby

[sbuster]

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.

[Mikelytics · Accepted solution]

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 · Accepted solution]

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]

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.

[Mikelytics · Accepted solution]

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.