Distinct count group by specific columns

Nigelbloomy — Wed, 10 Mar 2021 21:37:05 GMT

I am trying to create a measure (I don't have access to the dataset) to get a distinct count of status, by grouping year and ID. I also only want to count statuses selected by the user. I need to know for a given year and ID if there is more than one status, but be able to ignore some of those statuses when a user deselects them from the page filter.

I have tried several formulas and they all come close, but don't work all the way.

CALCULATE(DISTINCTCOUNT(Data[Investment Status]), ALLEXCEPT(Data,Data[Investment ID], Data[Year]))

This one works but doesn't change when a user filters the statuses on the page filter.

CALCULATE(DISTINCTCOUNT(Data[Investment Status]), ALLSELECTED(Data[Investment Status]))

This one allows the user to filter the results, but the grouping level is based on whatever is in the table in the viz. So for example with investment ID 25, there are 2 investment names, one approved and one draft. This formula misses the fact that for this year and ID there are 2 statuses.

COUNTROWS(FILTER(ALL(Data[Year], Data[Investment ID], Data[Investment Status]), ALLSELECTED(Data[Investment Status])))

I also tried this, but don't know how to get it to work at all. It looks promising, but gives me an error that multiple values were supplied when a single value was expected.

In this table the distinct count is what I am expecting when all statuses are selected:

Year	Solution	Investment ID	Investment Name	Investment Status	Project/OpEx	Labor/Non	Amount Planned	Distinct Count
2020	S1 solution	25	Investment 1	Approved	Project	Non	10	2
2020	S1 solution	25	Investment 2	Draft	Project	Non	10	2
2021	S1 solution	25	Investment 2	Draft	Project	Non	15	1
2022	S1 solution	25	investment 2	Draft	Project	Non	15	1
2020	S2 solution	26	Investment 3	Approved	Project	Non	10	1
2020	S2 solution	27	investment 4	Approved	Project	Non	5	2
2020	S2 solution	27	investment 4	Ready for division review	Project	Non	8	2
2020	S2 solution	28	investment 5	Approved	Project	Non	10	1
2021	S2 solution	26	investment 3	Approved	Project	Non	12	2
2021	S2 solution	26	investment 3	Ready for ICSC review	Project	Non	15	2
2021	S2 solution	27	investment 4	Ready for division review	Project	Non	10	1
2021	S2 solution	28	investment 5	Approved	Project	Non	10	1
2020	S3 solution	30	investment 6	Approved	Project	Non	10	1
2021	S3 solution	30	investment 6	Approved	Project	Non	10	1

This is what I would want to see if the user has chosen to only select Approved and Draft statuses:

Year	Solution	Investment ID	Investment Name	Investment Status	Project/OpEx	Labor/Non	Amount Planned	Distinct Count
2020	S1 solution	25	Investment 1	Approved	Project	Non	10	2
2020	S1 solution	25	Investment 2	Draft	Project	Non	10	2
2021	S1 solution	25	Investment 2	Draft	Project	Non	15	1
2022	S1 solution	25	investment 2	Draft	Project	Non	15	1
2020	S2 solution	26	Investment 3	Approved	Project	Non	10	1
2020	S2 solution	27	investment 4	Approved	Project	Non	5	1
2020	S2 solution	27	investment 4	Ready for division review	Project	Non	8
2020	S2 solution	28	investment 5	Approved	Project	Non	10	1
2021	S2 solution	26	investment 3	Approved	Project	Non	12	1
2021	S2 solution	26	investment 3	Ready for ICSC review	Project	Non	15
2021	S2 solution	27	investment 4	Ready for division review	Project	Non	10
2021	S2 solution	28	investment 5	Approved	Project	Non	10	1
2020	S3 solution	30	investment 6	Approved	Project	Non	10	1
2021	S3 solution	30	investment 6	Approved	Project	Non	10	1

Re: Distinct count group by specific columns

Nigelbloomy — Wed, 10 Mar 2021 23:16:43 GMT

This seems to work but I haven't tested it much. It would be annoying to add any extra columns to the all function. It would be more robust if you could specify the year and ID as the level of grouping.

CALCULATE(DISTINCTCOUNT(Data[Investment Status]), ALLSELECTED(Data[Investment Status]), ALL(Data[Investment Name]))

topic Distinct count group by specific columns in DAX Commands and Tips

Distinct count group by specific columns

Re: Distinct count group by specific columns