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 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 ...
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
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