Dynamic and categorized distinct counting

[tjalleph]

Hi all,

I'm fairly new to Power BI and trying to do something that could be called dynamic and categorized distinct couting. 

A simplified sample of my data would like this:

ID	Category	Type	Coded_answer
#1	A	X	0
#1	B	X	0
#1	C	X	1
#1	D	X	2
#1	E	X	0
#2	A	Y	0
#2	C	Y	0
#2	D	Y	0
#3	A	X	0
#3	B	X	0
#3	C	X	(null)
#3	D	X	2
#4	A	X	1
#4	B	X	0
#4	C	X	0

Then I'd want to a distinct couting of ID's and categorize them on the basis of the maximum of Coded_answer in that group. 

It would need to do so for many different slices of the data. Resulting tables for this dataset would have to look like:

Resulting tables
	max = 0	max = 1	max = 2
Total	1	1	2
Categories	max = 0	max = 1	max = 2
Cat A	3	1	0
Cat B	3	0	0
Cat C	2	1	0
Cat D	1	0	2
Cat E	1	0	0
Type	max = 0	max = 1	max = 2
X	1	0	2
Y	1	0	0

I've been trying to create two measures (see below) that do a distinct count based on the condition where the maximum is 2. That does work, because it's the highest value in the coded_answer. However, if I create a measure that does the same a distinct count where the maximum is 1, it would also count the ID #1, while there's also 2 in this ID. The reason to me seems that I'm only applying a filter. 

Could someone point me in the right direction?

Kind regards,

Tjalle

*measure: 

# max:2 = calculate(DISTINCTCOUNT(My_data[ID]), filter(My_data, [max_coded_answer]=2))

max_coded_answer = maxx(values(My_data[ID]), max(My_data[Coded_answer]))

[v-eachen-msft]

Hi @tjalleph ,

I found that there is a "null" value in your table. You could add CALCULATE(MAX('Table'[Coded_answer]),FILTER('Table','Table'[Coded_answer]>=0)) to your filter.

[sturlaws · Accepted solution]

Hi @tjalleph ,

you can write your measures like this:

m0 =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( 'Table'[ID] );
            "maxCodedAnswer"; CALCULATE ( MAX ( 'Table'[Coded_answer] ) )
        );
        [maxCodedAnswer] = 0
    )
)

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[tjalleph]

Works like magic! Thanks again.

To make sure i understand the concept: am I right when saying that we’re creating a temporary table with the distinct IDs and the maxCodedAnswer, that is subsequently filtered on the given maximum (in the example: 0)?

-Tjalle

[sturlaws]

Correct

[tjalleph]

Thanks! First results seems to be like I would expect them to be. I'm gonna try it on an additional dataset. Will come back early next week with the results.

Kind regards,

Tjalle