cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Vvelarde Super Contributor
Super Contributor

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!




View solution in original post

6 REPLIES 6
Vvelarde Super Contributor
Super Contributor

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!




Highlighted
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

Vvelarde Super Contributor
Super Contributor

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!




View solution in original post

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?

 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 147 members 1,696 guests
Please welcome our newest community members: