Comparing a value with a range in related table when another column matches

[Anonymous]

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.

[RobbeVL]

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

[Anonymous]

@RobbeVL,

No need to leave a general comment please!

Already tagged my question with IF and mentioned custom column in its body,

Thanks

[RobbeVL]

My main point was,
Create a sample pbix file...
Dont always expect minimum effort for maxiumum result...

[MFelix · Accepted solution]

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

[Anonymous]

@MFelixThanks!

The only part is that I have vriable Max values which makes it difficult to group and generalize them using the FirstBlank!

This is why I am confused

[MFelix]

Hi @Anonymous,

Not understanding your issue, what do you mean you have a varia ble max value?

I have tested out with several values and several levels of Max and min and the result was matchig.

Regards,
MFelix

[Anonymous]

Thanks @MFelix  for taking the time to reply. Actually in the pic I shared the value in the Max column varies based on the value in column F and Column Code. Maybe the best question I should have asked you was what Firstnonblank() function does in the measure? If you please explain it a little bit more I think it might be the most helpful hint

Thank you very much again @MFelix  and looking forward to hearing from you

[MFelix]

Hi @Anonymous,

Since the values you have on the lookpvalue has to compare values from 2 different columns and tables this is not possible using the LOOKUPVALUE function.

The FIRSTNONBLANK returns the first value on the table that is not blank when you add tje filter with your options it finds the first value that matches your criteria, so in this case you can only have an incorrect result if the ID, Min and Max column on the table you are making the search as the same values for the 3 columns.

Regards,
MFelix