If statement for columns in two tables without relationship

[twister8889]

Hi guys,

I have two tables that don't contain one relationship, but I need to create an if to validate Qty Month. For example: If Qty Month > Start Range and < End Range then New, If Qty Month < End Range then Middle else Old

[FrankAT]

Hi @twister8889 

what about the following solution?

Result = 
SWITCH(TRUE(),
    'Table'[Qty Mont] >= 0  && 'Table'[Qty Mont] < 3, "New",
    'Table'[Qty Mont] >= 3  && 'Table'[Qty Mont] < 6, "Middle",
    "Old"
)

Regards FrankAT

[twister8889]

@FrankAT  Thank you for your answer....

However, the range Start, End (0,3,6,..)  is dynamical, I can't put the values manually, I need to validate with columns from table2

[FrankAT]

Hi @twister8889
How does the dynamic change of the start and end values take place?
Regards FrankAT

[twister8889]

The table with range is loaded by Excel for each client, so I have a range different. Sometimes for one client, the N is 4, another can be 7.... So this data is dynamic by the client and I can't put this data manually. I think the best way, is compare where the month qty is on the range table

[FrankAT]

Hi @twister8889 

I think you can do it like this:

Result = 
CALCULATE (
    VALUES ( 'Range Table'[Status] ),
    FILTER (
        'Range Table',
        'Range Table'[Start Range] <= 'Table'[Qty Mont]
            && (
                'Range Table'[End Range] > 'Table'[Qty Mont]
                    || 'Range Table'[End Range] = BLANK ()
            )
    )
)

Regards FrankAT
(Proud to be a Datanaut)

[amitchandak]

@twister8889 , create a new table in table 1

new column = maxx(filter(table2, table1[Qty Month] >= table2[Start Range] && table1[Qty Month] <= coalesce(table2[End Range],9999999)),table2[status])

[twister8889]

Hi @amitchandak 

First of all, thank you for your answer

1-) I tried to do, but I have an error when I inserted the coalesce 

(cannot find name coalesce)

But without it, fine works.

2-) After my tests, I saw that I need one more validation, table2, table1[Qty Month] >= table2[Start Range] && table1[Qty Month] <= coalesce(table2[End Range],9999999) && table1[Status] = 1
It's possible increase the formula with this validation?