Forum Discussion
Dynamic and categorized distinct counting
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:
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.
5 Replies
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.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
- 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)?
-TjalleCorrect
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.
