Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |