LookupValue with variable to set column reference

Hi ,

can you try with below debug steps to solve your issue.

• Load your raw data table CAC_AllData into Power BI In Power Query, select all columns from PTC1 to PTC260.
• Right-click and choose Unpivot Columns Rename the resulting columns:
• Attribute → AttachmentName
• Value → PTC_Value
• You will now have a table with columns: SerialNumber, AttachmentName, PTC_Value
• Load or create a separate table called UsedAttachments with columns: SerialNumber, AttachmentName, Rank
• In Power BI model view, create relationships:
• Between CAC_Unpivoted[SerialNumber] and UsedAttachments[SerialNumber]
• Between CAC_Unpivoted[AttachmentName] and UsedAttachments[AttachmentName]
• Optionally, instead of 2 relationships, create a calculated column in both tables:
• Serial_Attach_Key = SerialNumber & "-" & AttachmentName
• Create a relationship on Serial_Attach_Key between the two tables

Create a calculated column in the unpivoted table:

IsUsed =
CALCULATE(
COUNTROWS(UsedAttachments),
FILTER(
UsedAttachments,
UsedAttachments[SerialNumber] = CAC_Unpivoted[SerialNumber] &&
UsedAttachments[AttachmentName] = CAC_Unpivoted[AttachmentName]
)
) > 0
Use this column to filter your visuals to show only rows where IsUsed = TRUE

Alternatively, create a calculated table with only matching rows:

FilteredPTCData =
FILTER (
CAC_Unpivoted,
CALCULATE (
COUNTROWS (UsedAttachments),
UsedAttachments[SerialNumber] = CAC_Unpivoted[SerialNumber],
UsedAttachments[AttachmentName] = CAC_Unpivoted[AttachmentName]
) > 0
)

If it is solved, please mark the helpful reply or share your solution and Accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

Chaithanya.