Table visual should dynamically respond to slicer selection

[aatish178]

Hi All, I am new to power bi., and I am in really need of your help on my below query., Plz have a look and help if possible.

I have 2 tables(with no active direct relationship)., shown below:

Table1:

DateForFiltering

2024-01-01

2022-05-26

2022-05-25

2021-12-13

2021-10-01

The "Single Select" option is enabled on above field

Table2:(It has different versions of product., once one version closes., the next version will get started immediatly on next day)

ProductNo StoreID    ValidFrom           ValidTo                Qty

257298        ST1         2020-06-05     2021-11-29            2369

257298        ST1         2021-11-30     2021-12-12            2370

257298        ST1         2021-12-13     2022-05-25            2433

257298        ST1         2022-05-25     9999-12-31            2311

Here, for each ProductNo and StoreID Unique combinations, I have to find out lates product based on Max of ValidTo Date and in addition to that this table2 should only show data where ValidFrom date is <=  selectedvalue of Table1 DateForFiltering so that if user changes the date from table1 field slicer., the table visual from table 2 should filter accordingly.

I have achieved this by using below expression

Latest version = var maxdate=MAX(Table1[DateForFiltering])

var ValidFrom= FILTER(ALLEXCEPT('Table2','Table2'[ProductNo],'Table2'[StoreID]),'Table2'[ValidFrom]<=maxdate)

var result=

CALCULATE(max('Table2'[ValidTo]),ValidFrom)

RETURN

if(MAX('Table2'[ValidTo])=result,1,BLANK())

Now the main problem coming when user selects some lesser date from "ValidFrom" Slicer.

My Expectation is, even if user selects multiple less dates from "ValidFrom" Slicer, the latest version should get calculate based on the selected values of the slicer. Below are the sss FYR

In this without selecting any validfrom date I am getting correct output

Screenshot2: With Issue

Here I have selected 2 dates from validfrom slicer., these dates are fulfilling the criteria of ValidFrom<=SelectedValue(DateForFiltering)

Hence in All version we have 2 rows., and latest version should show the 1st row with max of validTo(2022-05-25) , but the table visual is showing blank result.

Someone plz help me to bring the result in latest version table?!.. Thanks in advance 🙂

@Ashish_Mathur @quantumudit @quantumudit1 @v-huijiey-msft @gmsamborn @v-jialongy-msft @v-kaiyue-msft 

[amitchandak · Accepted solution]

@aatish178 , Change valid from

var ValidFrom= FILTER(allselected('Table2'),'Table2'[Item No] = max('Table2'[Item No]) && 'Table2'[RU] = max('Table2'[RU]) && 'Table2'[ValidFrom]<=maxdate)

[aatish178]

Wow Amit, the solution is working perfectly...I am really very very thankful to you...🙏 It has solved a big problem of mine