Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
4 years ago
Solved

Calculated Column with if statement in same table

Hi all!

I have a table that has the following structure:

Order NrGroupKeyTRUE / FALSE
100Group ARTRUE
100Group AZTRUE
100Group BRFALSE
101Group CZTRUE
102Group DRFALSE

 

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?

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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's avatar
      joshua1990
      Post Prodigy
      4 years ago

      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's avatar
        Jihwan_Kim
        Super User
        4 years ago

        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