Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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.
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?
Solved! Go to Solution.
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"
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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:
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
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"
)
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |