Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to 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.
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.
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.
Correct
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |