CODEWITHSUNDEEP
powerbi
Student of the Digital World
Student of the Digital World

Reputation: 3041

Power BI - Apply filters through values from another table and get the minimum date on another table

I have two Tables

Table 1
ColA ColB 
A     B  
B     C   
A     B 

Table 2 
ColA ColB Date
A    B    1/1/2011
A    B    1/3/2017

I have the Col A and ColB as two different filters from Table 1. I am trying to take the filter selected values from there and Identify the Minimum date for that combination in Table2.

Because of my data model, I am not able to use the ColA ColB from Table2 as filters on my front end.

Expected output when A and B are selected will be 1/1/2011.

Upvotes: 0

Views: 7203

Answers (3)

Ganesh Kumar
Ganesh Kumar

Reputation: 11

Try the below measure should do

Measure = CALCULATE(MIN(Table2[Date]),
                    FILTER(Table2,  
                               Table2[A]=SELECTEDVALUE(Table1[A],BLANK())
                            && Table2[B]=SELECTEDVALUE(Table1[B],BLANK())
                           )
                      )

Upvotes: 1

Jbowman
Jbowman

Reputation: 470

Will this work for you?

From Table2, create a new Table3 (by reference) with the three columns, combine ColA & ColB to new column CombinedAB, then sort (date descending) & filter the unique CombinedAB. Given your example, the new table would then have
ColA ColB Date CombinedAB A B 1/1/11 AB

Next, in Table1, combine ColA & ColB to NewAB, then join the Table1 & Table3 based on NewAB & CombinedAB and select the Date from Table3 fields.

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40204

You can calculate the minimum date over a filtered table.

A calculated column on Table 1 would look like this:

 = MINX(
       FILTER('Table 2',
           'Table 2'[ColA] = EARLIER('Table 1'[ColA]) &&
           'Table 2'[ColB] = EARLIER('Table 1'[ColB])),
       'Table 2'[Date])

If you want it as a measure, then replace EARLIER with MAX.

Upvotes: 1

Related Questions