Forum Discussion
Comparing a value with a range in related table when another column matches
Hello Gurus,
I have a situation as following:
I have two table as the screenshots you see here:
My final goal is to create a Custom Column in which it produces the values in Col F of the first table (1st screenshot), it has 7 values like F1, F2, F3, F7 and it's Text type, when the values in column Max of the second table(below screenshot) falls between the Min and Max of the first table.
So to sum up, I have made a connection through Column Key in both tables. I have 2 different conditions:
1. Check if Column Code of 2nd table matches with Column Code of first table (as relevant screenshots)
2. Check if the value in Column Max of the second table falls between the Min and Max columns of the first table
then it shows the relevant value in Column F of the first table(1st screenshot) in the custom column of the 2nd table. Just an extra note, finally I have Sum the values in final Qty based on their value in column F of the 2nd table to show in my visual.
Many thanks in advance.
P.s. values in col Final Qty, Min, Max and Max of both tables are Type Whole Number and everything else is Type text.
Hi Anonymous ,
Believe that the best option is to use the FIRSTNONBLANK formula applying a FILTER to the columns you need.
Add a custom column with the following code and see if it works.
F Code = CALCULATE ( FIRSTNONBLANK ( Table1[F]; 1 ); FILTER ( Table1; Table1[Code] = Table2[Code] && Table1[Max] >= Table2[Max] && Table1[Min] <= Table2[Max] ) )Regards,
MFelix
8 Replies
Hi,
You will need to merge the columns and create a custom column with an IF statement.
Create a sample pbix file, and I'd be happy to help further.
Robbe
