DISTINCT COUNT AND GROUP BY

[samnaw]

Hello:

I am trying to do GROUP BY in a DAX statement with DISTINCT COUNT. 

When I use this formula I get this:

CALCULATE

    (DISTINCTCOUNT(Table_1[Dim_1]),

        FILTER(Table_1,Table_1[Dim_1]="xyz")

        )

89 unique programs for YTD

Program	Count
Program_1	34
Program_2	20
Program_3	35
Total	89

But I would like this number to show up GROUP BY month:

Month	Count
September	78
October	88
Total	166

I want it to DISTINCT(COUNT) by Month and then SUM. 

Program	Count
Program_1	54
Program_2	40
Program_3	72
Total	166

[v-rzhou-msft]

Hi @samnaw ,

According to your statement, I still have some questions. I am confused about DISTINCTCOUNT YTD = 89, however DISTINCTCOUNT MONTH = 166. Please share a sample file with me and show me a screenshot with the result you want.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@samnaw First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8