check value with condition in another table

[Anonymous]

Hi,

my first table looks like:

Group	account_from	account_to	size_from	size_to
A	100	900
B			30	40
C

my second table is

account	size
7	35
22	87
250	100

output should be

account	size	Group
7	35	B	because, 35 is between 30-40
22	87	C	because, 22 is not between 100-900 and not between 30-40
250	100	A	because, 250 is between 100-900

Any idea how I can do this with DAX? 🙏

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a calculated column.

Group Calculated Column =
VAR _accountcondition =
    FILTER (
        'group',
        'group'[account_from] <= account[account]
            && 'group'[account_to] >= account[account]
    )
VAR _sizecondition =
    FILTER (
        'group',
        'group'[size_from] <= account[size]
            && 'group'[size_to] >= account[size]
    )
VAR _nocondition =
    FILTER (
        'group',
        'group'[account_from] = BLANK ()
            && 'group'[size_from] = BLANK ()
    )
RETURN
    SWITCH (
        TRUE (),
        COUNTROWS ( _accountcondition ) > 0, MAXX ( _accountcondition, 'group'[Group] ),
        COUNTROWS ( _sizecondition ) > 0, MAXX ( _sizecondition, 'group'[Group] ),
        MAXX ( _nocondition, 'group'[Group] )
    )

[Anonymous]

AWESOME! Thanks so much for your help. It works 😊