topic Re: Calculated Column with if statement in same table in DAX Commands and Tips

Calculated Column with if statement in same table

joshua1990 — Tue, 02 Aug 2022 16:45:08 GMT

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?

Re: Calculated Column with if statement in same table

Jihwan_Kim — Tue, 02 Aug 2022 17:51:14 GMT

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

Re: Calculated Column with if statement in same table

joshua1990 — Tue, 02 Aug 2022 18:06:32 GMT

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

Re: Calculated Column with if statement in same table

Jihwan_Kim — Tue, 02 Aug 2022 18:21:31 GMT

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