Forum Discussion

rjose's avatar
rjose
Frequent Visitor
4 years ago
Solved

Get a column from another table

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

CategorySub categoryValue
aa110
aa220
aa330
bb140
bb250
bb360

Table 2

CategorySegmentMinMax
a<10010
a10-301030
a>303099999999
b<20020
b20-402040
b>404099999999

 

 

Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    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

     

2 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    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