topic Re: finding max of date from filtered rows of table visual in DAX Commands and Tips

finding max of date from filtered rows of table visual

aatish178 — Wed, 19 Jun 2024 11:43:49 GMT

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

Re: finding max of date from filtered rows of table visual

lbendlin — Wed, 19 Jun 2024 21:22:21 GMT

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.

Re: finding max of date from filtered rows of table visual

aatish178 — Thu, 20 Jun 2024 06:09:13 GMT

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

Re: finding max of date from filtered rows of table visual

Anonymous — Thu, 20 Jun 2024 08:14:54 GMT

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.

Re: finding max of date from filtered rows of table visual

aatish178 — Thu, 20 Jun 2024 11:16:08 GMT

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.

Re: finding max of date from filtered rows of table visual

aatish178 — Thu, 20 Jun 2024 12:12:01 GMT

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

Re: finding max of date from filtered rows of table visual

aatish178 — Thu, 20 Jun 2024 12:21:55 GMT

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

Plz check this

Re: finding max of date from filtered rows of table visual

Anonymous — Fri, 21 Jun 2024 03:37:47 GMT

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.

Re: finding max of date from filtered rows of table visual

aatish178 — Fri, 21 Jun 2024 11:51:13 GMT

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

Re: finding max of date from filtered rows of table visual

Anonymous — Mon, 24 Jun 2024 01:53:27 GMT

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.