Forum Discussion
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) with one row highlighted that I would like to be able to identify and filter on. I.e. when functional currency does not equal invoiced currency, "False", when they match, "True" so i can show amt of $ with mismatching currencies.
Thinking this is a custom column but I have not seen a post yet on this forum like this scenario so figured it be helpful for many.
Tables are connected many to one so I can add them in the same table.
Functional currency (company hierarchy table)
Invoiced currency = ("currency code" vendor spend summary table) - connected by company key
DAX I tried but did not work (also not able to filter by a measure so this needs to be a custom column instead) -
6 Replies
Hi, biotechanalyst
Based on your description, I have created the following two tables:
The relationship is as follows:
You'll need to create a measure as follows:
Measure = IF(SELECTEDVALUE('Table2'[Functional currency])=SELECTEDVALUE('Table'[currency code]),1,0)Place this metric in the filter panel as follows:
I've provided the PBIX file used this time below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
this is the exact same Dax I've tried already
mismatch = IF(SELECTEDVALUE('Company Hierarchy'[Functional Currency]) = SELECTEDVALUE('Vendor Spend Summary'[Currency Code]), "True","False")
How is your solution different than the dax ive already tried? I also cannot make this measure a filter/slicer for the pageThanks Ritaf1983 ,your solutions is great.
Hi, biotechanalyst
If you need to add a calculated column, you can create a calculated column by following the DAX expression provided by super user for filtering in your report. The main reason for your error is that you are using the RELATED function on side 1 of the table. You should use this function on the * side of the table, as shown in the following figure:
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
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?
I must admit that I don't understand your question, and even less the image you attached...
If your goal is to create a calculated column using SELECTEDVALUE, it won't work.
I also attached the file itself in the first response so you can see exactly what's being done.
If that still doesn't help, please attach a link to a sample file with your logic.
