finding max of date from filtered rows of table visual

[aatish178]

Hi All, I have 2 tables, table 1 consisting of only single field as shown below:

DateForFiltering
2024-07-07
2022-05-26
2022-05-25
2021-12-13
2021-12-12
2021-12-01
2021-11-30
2021-11-29
2021-10-01
and there is always one select option enabled for this date in slicer.

Table2 has below data:

EmpNo CompCode CompNo      Valid From         Valid To         Salary
257298      BE            10035         2020-06-05       2021-11-29       2369
257298      BE            10035         2021-11-30       2021-12-12       2370
257298      BE            10035         2021-12-13       2022-05-25       2433
257298      BE            10035         2022-05-26       9999-12-31       2311
257298      BE            21174         2021-11-12       2022-02-02       1976
257298      BE            21174         2022-02-03       9999-12-31       2298
Here for each EmpNo,CompCode and CompNo unique combination I have to find out the latest version of data based on max of valid to date., but this valid to date column should only show those dates which are less than or equal to selected date from DateForFiltering field., and the dates which are greater than DateForFiltering , it should show it as Blank.

I have achieved date filtering using below measure:

Valid To filter date=
VAR DateCheck =
CALCULATE (       
             SELECTEDVALUE ( Table1[DateForFiltering] )
        )
   
RETURN
    IF (
        MAX ('Table2'[Valid To] ) <= DateCheck,
        MAX (''Table2'[Valid To] ),
      BLANK()
       )
The 2nd condition is Valid From date <= DateForFiltering.
I have achieved this using measure on salary as below:
CALCULATE(AVERAGE('Table2'[Salary]),FILTER('Table2',
                        'Table2'[Valid From]<=MAX(Table1[DateForFiltering])))
 
Now table visual will get filtered as per different selected date from DateForFiltering column. Till here the solution is working fine.,
Now from that filtered result., again I want to find out the max of valid to date for each EmpNo,CompCode and CompNo combination., but I am unable to achieve it dynamically., because the filtered result is still considering 9999-12-31 as max of valid to date.,
Lets understand this with example., I have selected 2021-12-13 from the DateForFiltering column., hence table visual will automatiically show me below result:
EmpNo    CompCode CompNo        Valid From         Valid To             Salary
257298      BE              10035             2020-06-05        2021-11-29       2369
257298      BE              10035             2021-11-30        2021-12-12       2370
257298      BE              10035             2021-12-13        2022-05-25       2433
257298      BE              21174             2021-11-12        2022-02-02       1976
this is the result of ALL VERSION., that i am able to achieve.,
Now from this filtered result., I want to find out Latest Version for EmpNo, CompCode,CompNo unique combination based on Max of Valid To Date(2022-05-25 and 2022-02-02., this i had shown in above table for understanding purpose., but in real it will display as blank as per the mentioned logic)., but power bi still considering 9999-12-31 as the max valid to date.
Can someone plz help me achieving this.
In Short , once I filtered Table2 by Table1 DateFor Filtering., and whatever result i got., that result should get consider to calculate max of valid to date., hence the final result would be
EmpNo     CompCode     CompNo     Valid From           Valid To                                            Salary
257298         BE                10035          2021-12-13     2022-05-25(will be shown as blank)      2433
257298         BE                21174          2021-11-12     2022-02-02(will be shown as blank)      1976

Plz help if possible., thanks in advance

@Ashish_Mathur @quantumudit @quantumudit1 @Anonymous @gmsamborn @v-jialongy-msft @Anonymous 

[aatish178]

Hi All, I have 2 tables without active relationship with each other. Table 1 has below data.

Table 1:

IntervalDate

2024-07-07

2022-05-26

2022-05-25

2021-12-13

2021-12-12

2021-12-01

2021-11-30

2021-11-29

2021-10-01

this date is used in slicer with setting of single select.

Table2: It has different versions of Product No based on valid from and valid to date., when one version closes, another version starts on very next day.,

and my goal is to find out the latest version among it for each Product No combination.

Product No   Valid From         Valid To           ProductQty

257298          2020-06-05     2021-11-29           100

257298          2021-11-30     2021-12-12           150

257298          2021-12-13     2022-05-25           175

257298          2022-05-26     9999-12-31           200

131278          2021-12-10     2022-12-30          500

131278          2022-12-31     9999-12-31           350

Here I have 2 uniqe products so the latest version of it based on max of valid to date would be:

Product No   Valid From         Valid To           ProductQty

257298          2022-05-26     9999-12-31           200

131278          2022-12-31     9999-12-31           350

Till here I am able to achive the output., The real problem is coming when the Table 2 is getting filtered by the IntervalDate of Table 1

Table 2 should only show the data having valid from date < = selected date of Table 1.

To achieve that I had used calculated measure on ProductQty Column as below:

Product qty = CALCULATE(AVERAGE('Table2'[ProductQty]),FILTER('Table2',

                        'Table2'[Valid From]<=MAX(Table1[IntervalDate ])))

So if I select IntervalDate as 2022-05-25, I wll get below output based on filtering(I am able to achieve this)

Product No   Valid From         Valid To           ProductQty

257298          2020-06-05     2021-11-29           100

257298          2021-11-30     2021-12-12           150

257298          2021-12-13     2022-05-25           175

131278          2021-12-10     2022-12-30           500

Now from this result the latest version should get calculated based on Max of valid to date(2022-05-25 and 2022-12-30 for each Product No.) that is below final output:

Product No   Valid From         Valid To           ProductQty

257298          2021-12-13     2022-05-25           175

131278          2021-12-10     2022-12-30           500

But power bi is still considering 9999-12-31 as the max of valid to date. This dynamic filtering I am not able to achieve from already filtered result.

To understand it more., lets take one more example. IntervalDate: 2021-12-12, the filtered data would be:

Product No   Valid From         Valid To           ProductQty

257298          2020-06-05     2021-11-29           100

257298          2021-11-30     2021-12-12           150

131278          2021-12-10     2022-12-30            500

Now from this result PBI should calculate Max of Valid To and should show below rows as final result

Product No   Valid From         Valid To           ProductQty

257298          2021-11-30     2021-12-12           150

131278          2021-12-10     2022-12-30            500

Can someone plz help me achieving this dynamic filtering based on already filtered data. Thanks in advance. 

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

[Anonymous]

Hi, @aatish178 

Based on your description, I used the following sample data:

table1:

table2:

I've created a measure using the following DAX expression to achieve the desired result you were expecting above:

Measure2 =
VAR _seleted_date =
    SELECTEDVALUE ( Table1[IntervalDate] )
VAR _table =
    FILTER ( ALL ( Table2 ), 'Table2'[Valid From] <= _seleted_date )
VAR _table1 =
    SUMMARIZE (
        _table,
        Table2[Product No ],
        "maxValid from",
            VAR _current_No =
                SELECTEDVALUE ( Table2[Product No ] )
            VAR _max_date =
                MAXX (
                    FILTER ( _table, 'Table2'[Product No ] = _current_No ),
                    'Table2'[Valid From]
                )
            RETURN
                _max_date,
        "maxValid to",
            VAR _current_No =
                SELECTEDVALUE ( Table2[Product No ] )
            VAR _max_date =
                MAXX (
                    FILTER ( _table, 'Table2'[Product No ] = _current_No ),
                    'Table2'[Valid To]
                )
            RETURN
                _max_date,
        "qty",
            VAR _current_No =
                SELECTEDVALUE ( Table2[Product No ] )
            RETURN
                MAXX (
                    FILTER ( _table, 'Table2'[Product No ] = _current_No ),
                    'Table2'[ProductQty]
                )
    )
RETURN
    IF (
        SELECTEDVALUE ( Table2[Valid From] )
            IN SELECTCOLUMNS ( _table1, [maxValid from] ),
        MAXX (
            FILTER (
                _table1,
                'Table2'[Product No ] = SELECTEDVALUE ( Table2[Product No ] )
            ),
            [qty]
        ),
        BLANK ()
    )

Here's what you can expect:

I looked at your DAX expression and you seem to want the qty average for each Product No at 'Table2'[Valid From]<=MAX(Table1[IntervalDate], then you can use the following DAX expression:

MEASURE =
VAR _seleted_date =
    SELECTEDVALUE ( Table1[IntervalDate] )
VAR _table =
    FILTER ( ALL ( Table2 ), 'Table2'[Valid From] <= _seleted_date )
VAR _table1 =
    SUMMARIZE (
        _table,
        Table2[Product No ],
        "maxValid from",
            VAR _current_No =
                SELECTEDVALUE ( Table2[Product No ] )
            VAR _max_date =
                MAXX (
                    FILTER ( _table, 'Table2'[Product No ] = _current_No ),
                    'Table2'[Valid From]
                )
            RETURN
                _max_date,
        "maxValid to",
            VAR _current_No =
                SELECTEDVALUE ( Table2[Product No ] )
            VAR _max_date =
                MAXX (
                    FILTER ( _table, 'Table2'[Product No ] = _current_No ),
                    'Table2'[Valid To]
                )
            RETURN
                _max_date,
        "qty",
            VAR _current_No =
                SELECTEDVALUE ( Table2[Product No ] )
            RETURN
                AVERAGEX (
                    FILTER ( _table, 'Table2'[Product No ] = _current_No ),
                    'Table2'[ProductQty]
                )
    )
RETURN
    IF (
        SELECTEDVALUE ( Table2[Valid From] )
            IN SELECTCOLUMNS ( _table1, [maxValid from] ),
        MAXX (
            FILTER (
                _table1,
                'Table2'[Product No ] = SELECTEDVALUE ( Table2[Product No ] )
            ),
            [qty]
        ),
        BLANK ()
    )

Here are the results:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aatish178]

Hi, Thanks for your support :), but the solution is failing at last step:

RETURN
    IF (
        SELECTEDVALUE ( Table2[Valid From] )
            IN SELECTCOLUMNS ( _table1, [maxValid from] ),
        MAXX (
            FILTER (
                _table1,
                'Table2'[Product No ] = SELECTEDVALUE ( Table2[Product No ] )
            ),
            [qty]
        ),
        BLANK ()
    )

Here the moment I am selecting valid from, less than the current valid from date., it is showing as blank.,

My expectation is for selected valid from date from slicer., if it is fitting in IntervalDate Criteria, then table visual show that entry as well., but here it is showing blank

Can you plz help me know hjow can i fix it

[Anonymous]

Hi @aatish178 ,

First of all, many thanks to @lbendlin  for your very quick and effective replies.

Based on my testing, please try the following methods again:

1.Create the simple tables.

2.Create the new measure to filter the Valid to date and max of date.

Max Valid To Date = 
VAR sel_date = SELECTEDVALUE(Table1[DateForFiltering])
var t2_date = SELECTEDVALUE(Table2[Valid To])
VAR result = CALCULATE(MAX(Table2[Valid To]), FILTER(ALLEXCEPT(Table2, Table2[Combination]), Table2[Valid To] <= sel_date && Table2[Valid From] <= sel_date))
RETURN
IF(t2_date = result,result,BLANK())

3.Drag the Table1 date field into the slicer visual.

4.Select the 2022-05-25. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aatish178]

Dynamic filtering of table visual in power bi base... - Microsoft Fabric Community

Plz check this

[aatish178]

Hie Thanks for your reply and efforts that you put in. Really appreciate it., but unluckily the solution is not working as expected., I will simplify my question in another new post.

[Anonymous · Accepted solution]

Hi @aatish178 ,

Based on my testing again, please try the following methods again:

1.Create the new measure to filter the Valid from date.

Latest result = 
VAR sel_date = SELECTEDVALUE(Table1[IntervalDate])
var t2_date = SELECTEDVALUE(Table2[Valid From])
VAR result = CALCULATE(SELECTEDVALUE(Table2[Valid From]), FILTER(Table2, Table2[Valid From] <= sel_date))
RETURN
IF(t2_date = result, 1, 0)

2.Drag the measure into the filters pane. Enter the show items is 1.

3.Create the new measure to filter the max date.

Max Valid Date = 
VAR sel_date = SELECTEDVALUE(Table1[IntervalDate])
var t2_date = SELECTEDVALUE(Table2[Valid From])
VAR result = CALCULATE(MAX(Table2[Valid From]), FILTER(ALLEXCEPT(Table2, Table2[Product No]), Table2[Latest result] = 1))
RETURN
IF(t2_date = result, 1, 0)

4.Drag the measure into the filters pane. Enter the show items is 1.

5.If you select the valid from date, the date should be contained in the filter table visual.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

DO NOT use dates like 9999-12-31 - they will cause your calendar table to have over 2 MILLION rows, for no good reason.  Use Blanks instead.

[aatish178]

There is a need to show this date in order for users to understand the version is still valid in the market... Hence it will be the latest version., my requirement is if user selects some less date from Date for filtering column and once he gets filtered data in table visual having dates other than 9999-12-31, from that date range the maximum of date should get calculated. That is something I am not able to achieve