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.
Hey 🙂
My table follows this structure:
Group | Id | Classification | Machine |
A | A10 | 1 | machine_1 |
A | A08 | 1 | machine_1 |
B | B81 | 2 | machine_2 |
A | A10 | 1 | machine_1 |
B | B81 | 2 | machine_2 |
C | C12 | 2 | machine_3 |
C | C15 | 1 | machine_1 |
A | A13 | 1 | machine_1 |
B | B81 | 2 | machine_2 |
C | C15 | 1 | machine_2 |
A product has only one id, but that id can be repeated several times in my table as it can receive different classifications over time. I want to create a counter measure that counts the number of Groups who has unique Ids with a classification repeated 3 or more times for the same machine.
Based on my example table above, this counter would show me the value: 1.
Because only in Group A has 3 differents Products (A08, A10 and A13) wich the same classifications and same machine (classification 1 and machine_1).
Can someone help me?
PS: Group B don't have 3 different products with the same classification, but it has the same product repeated 3 times (B81), so it does not count to the measure.
Solved! Go to Solution.
Hi, @Anonymous
Try measure as below:
Count_group =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Group] ),
FILTER (
'Table',
CALCULATE (
DISTINCTCOUNT ( 'Table'[Id] ),
ALLEXCEPT ( 'Table', 'Table'[Group], 'Table'[Classification], 'Table'[Machine] )
) >= 3
)
)
Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Try measure as below:
Count_group =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Group] ),
FILTER (
'Table',
CALCULATE (
DISTINCTCOUNT ( 'Table'[Id] ),
ALLEXCEPT ( 'Table', 'Table'[Group], 'Table'[Classification], 'Table'[Machine] )
) >= 3
)
)
Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
Create the following measure to get the desired results:
Groups Count =
CALCULATE (
DISTINCTCOUNT ( table11[Group ] ),
FILTER (
ADDCOLUMNS (
Table11,
"Check",
VAR __id =
CALCULATE (
DISTINCTCOUNT ( table11[ Id ] ),
ALLEXCEPT ( Table11, Table11[Group ] )
)
VAR __class =
CALCULATE (
DISTINCTCOUNT ( table11[ Classification] ),
ALLEXCEPT ( Table11, Table11[Group ] )
)
VAR __machine =
CALCULATE (
DISTINCTCOUNT ( table11[ Machine] ),
ALLEXCEPT ( Table11, Table11[Group ] )
)
RETURN
__id >= 3
&& __class = 1
&& __machine = 1
),
[Check] = TRUE ()
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |