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.
I have this dataset:
Group1 | Group2 | Group3 | Type |
A | A1 | A11 | T1 |
A | A1 | A11 | T2 |
A | A1 | A11 | T3 |
A | A1 | A11 | T4 |
A | A1 | A12 | T1 |
A | A1 | A12 | T2 |
A | A1 | A12 | T3 |
B | B1 | B11 | T1 |
B | B2 | B21 | T1 |
B | B2 | B21 | T2 |
B | B2 | B21 | T3 |
I summarized the number of types per Group3 items:
Group1 | Group2 | Group3 | Count |
A | A1 | A11 | 4 |
A | A1 | A12 | 3 |
B | B1 | B11 | 1 |
B | B2 | B21 | 3 |
I created two measures. One is [Type Count] = distinctcount([Type]). The other is [Group3 Count] = calculate(distinctcount([Group3]), [Type Count] >= 3).
If I create a matrix visual (with filter [Item Count] is greater or equal to 3), the result looks like this:
Group1/Group2 | Item Count |
- A | 7 |
- A1 | 7 |
A11 | 4 |
A12 | 3 |
- B | 3 |
- B2 | 3 |
B21 | 3 |
This is correct.
I need to create table visuals to show [Group3 Count].
If I create a table visual for Group1, the result should look like this:
Group1 | Group3 Count |
A | 2 |
B | 1 |
If I create a table visual for Group2 with a slicer for Group1, it should look like this:
Group1 slicer = A
Group2 | Group3 Count |
A1 | 2 |
B2 | 1 |
But for both tables, I'm getting no results. I need some advice on how to accomplish this. Thank you.
Solved! Go to Solution.
Create a measure
[Type Count] = Count(Table [Type])
Then create a measure
calculate(Countx(filter( Values(Table[Group3]), [Type Count] >= 3) , [Group 3]) )
This should work with first tables group 1, 2 etc
Hi @jabrillo ,
Please try:
Measure =
var _a = SUMMARIZE('Table','Table'[Group1],'Table'[Group2],'Table'[Group3],"Count",COUNT('Table'[Type]))
return COUNTX(FILTER(_a,[Count]>=3),[Count])
Output:
You mentioned that the second visual uses a slicer with Group1=A. If so, B2 does not appear in the visual, do I misunderstand?
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Create a measure
[Type Count] = Count(Table [Type])
Then create a measure
calculate(Countx(filter( Values(Table[Group3]), [Type Count] >= 3) , [Group 3]) )
This should work with first tables group 1, 2 etc
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 |
---|---|
106 | |
98 | |
80 | |
67 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |