Get a column from another table

[rjose]

Hi,

I do have 2 tables with many to many relationship in my data model (connected using bridge table that is, a table containing unique values of column 'Category'). I want to get column 'Segment' from table 2 to table 1 where table1.category = table2.category && value between min AND max. Any idea on how to achieve this using DAX? Any help is highly appreciated.

Table 1

Category	Sub category	Value
a	a1	10
a	a2	20
a	a3	30
b	b1	40
b	b2	50
b	b3	60

Table 2

Category	Segment	Min	Max
a	<10	0	10
a	10-30	10	30
a	>30	30	99999999
b	<20	0	20
b	20-40	20	40
b	>40	40	99999999

[Samarth_18 · Accepted solution]

HI @rjose ,

Create a column like below:-

Column =
CALCULATE (
    MAX ( _Table2[Segment] ),
    FILTER (
        ALL ( _Table2 ),
        [Category] = _Table1[Category]
            && ( [Value] > _Table2[Min]
            && [Value] <= _Table2[Max] )
    )
)

Output:-

Note:- Please share the expected output if this is not matching with your expected output. 🙂

Thanks,

Samarth

[rjose]

Thank you!