Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
uc516
Frequent Visitor

Need a solution for tagging rows based on filtering of others

Morning all,

 

I'm trying to set up a tracker that checks part traceability status. The data lists part numbers and the informations that is collected for each part on arival.

 

The task:

There are duplicate Part Numbers in the 1st column, as some parts have both Part Number and Supplier Serial Number collected. My goal is to label all parts that have Supplier Serial Numbers collected as "Traceable".

See example image below:

 

uc516_1-1669628451463.png

 

I managed to build the tracker in excel, but now I'm trying to convert it to a Power BI report.

The lines below filter out the table for a specific part number, then check if the colulmn "Data Collected" has a row containing "Supplier Serial No." and return a true/false answer correctly.

uc516_2-1669628751690.png

 

 

The issue:

 

1. The IF True action doesnt tag only the Part Number: 222 rows but all rows as "Traced".

2. I can't figure out a way to filter the table based on the individual Part Number values dynamically. I used a specific part number in the line to test if the rest of the logic works.

 

Anyone else had a similar problem? Any suggestions to tackle it?

1 ACCEPTED SOLUTION
mangaus1111
Solution Sage
Solution Sage

Hi @uc516 ,

you can try this calculated column

Column = 
IF(
COUNTROWS(
FILTER('Table',
      'Table'[Part Number] = EARLIER('Table'[Part Number]) && 'Table'[Data Collected] = "Supplier Serial No."
)) > 0,
"Traceable",
"Not Traceable"
)

mangaus1111_0-1669631370926.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yinliw-msft
Community Support
Community Support

Hi @uc516 ,

 

You can try this method:

New a column:

Traceability Status =
VAR _SSN =
    CALCULATE (
        SUM ( 'Plant Status'[Part Number] ),
        FILTER (
            'Plant Status',
            'Plant Status'[Data Collected] = "Supplier Serial No."
        )
    )
RETURN
    IF ( 'Plant Status'[Part Number] = _SSN, "Traceable", "Not Traceable" )

Then new a measure to change the color of background:

Color =
SWITCH (
    TRUE (),
    MAX ( 'Plant Status'[Traceability Status] ) = "Traceable", "#E1FFDE",
    MAX ( 'Plant Status'[Traceability Status] ) = "Not Traceable", "#FA5555"
)

The result is:

vyinliwmsft_1-1669704494527.png

 

vyinliwmsft_0-1669704478508.png

vyinliwmsft_2-1669704508484.png

 

 

Hope this helps you.

Here is my PBIX file.

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

Thank you for the extensive work and help.

 

I think in my attempt to explain the problem as simply as I can, I think i might have overdone it.

As there are also other Data Collection entries such as Trim Code and Supplier Code etc which alters the output.

 

I've managed to find a solution using a new table filtered out for supplier serials and then did an IF(ISNUMBER(LOOKUP to tag the matched part numbers, but @mangaus1111's solution seems to have yielded the same result within a few lines.

 

I'll definietly use your method of colour coding though, i wasn't aware of the method before, always used a new IF statement column to rename the entries for colour codes.

 

Appreciate your help

mangaus1111
Solution Sage
Solution Sage

Hi @uc516 ,

you can try this calculated column

Column = 
IF(
COUNTROWS(
FILTER('Table',
      'Table'[Part Number] = EARLIER('Table'[Part Number]) && 'Table'[Data Collected] = "Supplier Serial No."
)) > 0,
"Traceable",
"Not Traceable"
)

mangaus1111_0-1669631370926.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello,

 

Thank you for the solution.

Later last night I came up with a solution using a table that pulls in part numbers filtered down for "supplier serial number" and then did an IF(ISNUMBER(LOOKUP()), "Traceable" , "Not Traceable") of the part numbers from the original table.

Your solution seems to yield the same results, avoiding the need for a separate table. Amazing job!

 

Thank you again

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.