How to calculate a boolean column in DAX with specific filter

[SeHi]

Hi all,

I have the following challange. My input table is:

Key	Labels	Cluster
100	red	B
100	white	A
101	white	A
102	red	B
102	blue	C
102	white	A
103	orange	A
104	yellow	A
104	orange	A

I want to calculate an additional column ("Done by cluster A only?") in DAX which contains a boolean result

The result table shall look like:

Key	Labels	Cluster	Done by cluster A only?
100	red	B	0
100	white	A	0
101	white	A	1
102	red	B	0
102	blue	C	0
102	white	A	0
103	orange	A	0
104	yellow	A	1
104	orange	A	1

Requirement is: Indentify the Cluster = A values where no other clusters are asigned to the same key. If cluster=A has multiple assignments to a single key (but no other clusters are assigned to this key), this should also return true.

Any suggestions to solve this in DAX?

Thanks for your support!

[rajendraongole1 · Accepted solution]

Hi @SeHi - create a calculated colum as below:

Done by cluster A only? =

VAR CurrentKey = 'clust'[Key]

VAR OtherClusters =

    CALCULATETABLE(

        DISTINCT('clust'[Cluster]),

        'clust'[Key] = CurrentKey,

        'clust'[Cluster] <> "A"

    )

RETURN

    IF(

        COUNTROWS(OtherClusters) = 0,

        1,

        0

    )

Hope it works.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!