Forum Discussion

biotechanalyst's avatar
biotechanalyst
Frequent Visitor
2 years ago

Filter report when one column does not match another column from a different table

Hello,   Hoping you can help! I want to determine all invoices that have mismatching functional and invoiced currencies. Ex. one row of many thousands of invoices (no data file to keep privacy) ...
Need Help
Ritaf1983's avatar
Ritaf1983
Icon for Super User rankSuper User
2 years ago

Hi biotechanalyst 

The SELECTEDVALUE function is commonly used in measures because it can capture the value selected in a visualization or slicer and perform dynamic calculations based on user interaction.

In your case, since you're dealing with a calculated column, you can utilize the RELATED function. This function can navigate to a related table and retrieve the corresponding value based on the key. It's somewhat analogous to the VLOOKUP function in Excel.

For example :
A model with the logic like yours :

The calculated column :
Formula :

mismatch = if(RELATED('Companies'[currency code])=[Currency], True,False)
Result :

The pbix is attached

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

 

biotechanalyst's avatar
biotechanalyst
Frequent Visitor
2 years ago

Are you supposed to make a serperate table for this? If I simply add the mismatch function youve provided (i.e. mismatch = IF(SELECTEDVALUE('Company Hierarchy'[Functional Currency]) = SELECTEDVALUE('Vendor Spend Summary'[Currency Code]), "True","False") I cant get the measure references correct?