cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
eravelo Frequent Visitor
Frequent Visitor

How do I compare diferent rows to show if the condition is true?

Hello, I have a dataset that looks something like this:

 

Captura.PNG

 

what i need to do is for every row i need to compare, if the plant code apears in repeated ocasions in the table with the 3 different zones, i need the 3rd column to say "True" but if it only apears in 2 different zones or in just one, then it should return false... in this example the result should be something like this:

 

Captura2.PNG

 

in the table,  the plant 1010 appears for the 3 zones, also the plant 1011, ppears on the 3 zones but the rest of the plants appears just in one or two zones, even thought they appear multiple times in the table.

 

thanks for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How do I compare diferent rows to show if the condition is true?

@eravelo

 

Ok. In Dax using a calculated Column

 

Flag =
IF (
    COUNTROWS (
        SUMMARIZE (
            FILTER ( Table1, Table1[Plant Code] = EARLIER ( Table1[Plant Code] ) ),
            Table1[Plant Code],
            Table1[Zone]
        )
    )
        >= 3,
    "True",
    "False"
)

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




6 REPLIES 6
Super User
Super User

Re: How do I compare diferent rows to show if the condition is true?

@eravelo

 

Hi, In Query Editor? or Using Dax?




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




eravelo Frequent Visitor
Frequent Visitor

Re: How do I compare diferent rows to show if the condition is true?

Hi, I would accept wichever solution that you could bring to me... If one of them is easier, i would preffer that one.

Thanks

Super User
Super User

Re: How do I compare diferent rows to show if the condition is true?

@eravelo

 

Ok. In Dax using a calculated Column

 

Flag =
IF (
    COUNTROWS (
        SUMMARIZE (
            FILTER ( Table1, Table1[Plant Code] = EARLIER ( Table1[Plant Code] ) ),
            Table1[Plant Code],
            Table1[Zone]
        )
    )
        >= 3,
    "True",
    "False"
)

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




mow700 Regular Visitor
Regular Visitor

Re: How do I compare diferent rows to show if the condition is true?

I'd use the query editor for this task.  Use transformation steps to create a table with 3 columns for each region containing the [Plant Code].  The result should leave NULL values for regions with no plants.  This is an improved approach, since the boolean column in your design only identifies plants with a missing region, but does not identify which regions need plants.

 

Here is a good guide on how to use the transformation steps:

https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-1-5-cleaning-irregular-data/

 

cthurston Regular Visitor
Regular Visitor

Re: How do I compare diferent rows to show if the condition is true?

Create a Column:

True or False = IF(
    CALCULATE(
        DISTINCTCOUNT(Table1[Zone]
            ),
            ALLEXCEPT(Table1,Table1[Plant Code]
                )
                )>2,TRUE(),FALSE()
                )

eravelo Frequent Visitor
Frequent Visitor

Re: How do I compare diferent rows to show if the condition is true?

This gives me the exact solution for what i wanted, do you have any idea of how i can make the format do a calculation for the ones that are true?