Calculated Column with if statement in same table

[joshua1990]

Hi all!

I have a table that has the following structure:

Order Nr	Group	Key	TRUE / FALSE
100	Group A	R	TRUE
100	Group A	Z	TRUE
100	Group B	R	FALSE
101	Group C	Z	TRUE
102	Group D	R	FALSE

THere is a column for the Order Nr, Group and Key.

Now I would like to add the last column with TRUE / FALSE based in this requirements:

TRUE if there is any entry for a Order Nr in the same Group and Key = 'Z'.

As you can see above there is Order Nr 100 in the Group A with 2 entries. One with R and one with Z. Therefor all rows for the order 100 in the same group get TRUE. Otherwise FALSE.

How is this possible as a calculated column?

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

True False CC = 
COUNTROWS (
    FILTER (
        Data,
        Data[Order Nr] = EARLIER ( Data[Order Nr] )
            && Data[Group] = EARLIER ( Data[Group] )
            && CONTAINS (
                FILTER (
                    Data,
                    Data[Order Nr] = EARLIER ( Data[Order Nr] )
                        && Data[Group] = EARLIER ( Data[Group] )
                ),
                Data[Key], "Z"
            )
    )
) >= 1

[joshua1990]

@Jihwan_Kim Awesome, that works perfectly fine! If I want to include another Key into this function like "W", how is this possible?

[Jihwan_Kim · Accepted solution]

Hi,

Do you mean, Z or W returns true?

Please check the attached pbix file.

True False CC =
COUNTROWS (
    FILTER (
        Data,
        Data[Order Nr] = EARLIER ( Data[Order Nr] )
            && Data[Group] = EARLIER ( Data[Group] )
            && OR (
                CONTAINS (
                    FILTER (
                        Data,
                        Data[Order Nr] = EARLIER ( Data[Order Nr] )
                            && Data[Group] = EARLIER ( Data[Group] )
                    ),
                    Data[Key], "Z"
                ),
                CONTAINS (
                    FILTER (
                        Data,
                        Data[Order Nr] = EARLIER ( Data[Order Nr] )
                            && Data[Group] = EARLIER ( Data[Group] )
                    ),
                    Data[Key], "W"
                )
            )
    )
) >= 1