filtering with unconnected table

[Anonymous]

Hi all

I need to make my calculated colomn filter on valid_from and valid_to in a unrelated tabel. My plan was to add datesbetween after value_id filter, to retrive correct value to correct dateperiod. but this doesen´t work

Any ides?

tabel1 this is my goal.

some_stuff	date	some_value	calculated colomn (code below)
a	20201001	10	100
b	20201001	10	150
b	20201101	10	100

table2 the other table

valid_from	valid_to	value_id	some_value
20201001	20991231	1	10
20201001	20201031	2	15
20201101	20991231	2	10

calculated colomn =

'tabel1'[somevalue]

     * SWITCH (

           TRUE (),

           'tabel1'[some_stuff] = "a",

               CALCULATE (

                     MIN ( 'table2'[somevalue] ),

                     'table2'[value_id] = 1

               ),

               'tabel1'[some_stuff] = "b",

            CALCULATE (

            MIN ( 'table2'[somevalue] ),

             'table2'[value_id] = 2,

          )

)

BG

/Måns

[amitchandak · Accepted solution]

@Anonymous , Try a new column like this in table 1

maxx(filter(table2, ((Table1[some_stuff] ="a" && table2[value_id] = 1) || ( Table1[some_stuff] ="b" && table2[value_id] = 1 ))
&& Table1[date] >= Table2[valid_from] && Table1[date] <= Table2[valid_to] ), Table2[some_value])

[Anonymous]

That did it : )
thanks a bunch @amitchandak 

[Anonymous]

For the sake of clearity.

table 1 col date has 8 digits, cant get the col some_value to respect col dates space. I guess col some_value realy want´s a date : )