Count the number of equal classification for a unique id

[Anonymous]

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

• The Group column informs the group which the product of that row belongs.
• The Id column informs which product was created.
• The Classification column informs you if the product is good, average or bad (1, 2 or 3).
• The Machine column informs which machine the product was created on.

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.

[v-easonf-msft · Accepted 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.

[Fowmy]

@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 ()
    )
)