topic Re: How to obtain the max value for several rows with the same id with condition in DAX Commands and Tips

How to obtain the max value for several rows with the same id with condition

SivaSatya — Thu, 11 Nov 2021 13:52:49 GMT

Hello, I need to calculate in a new colum/new measure the max date for the same id within a table and also check if it is less than today. if yes, that value/date to be taken else check the next max date and check if is less than today and so on....

Re: How to obtain the max value for several rows with the same id with condition

Jihwan_Kim — Thu, 11 Nov 2021 15:48:05 GMT

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

New Table =

VAR newtable_onorbeforetoday =

FILTER ( Data, Data[Date] <= TODAY () )

VAR groupbytable =

GROUPBY (

newtable_onorbeforetoday,

Data[ID],

"@maxdate", MAXX ( CURRENTGROUP (), Data[Date] )

)

RETURN

CALCULATETABLE ( Data, TREATAS ( groupbytable, Data[ID], Data[Date] ) )

Re: How to obtain the max value for several rows with the same id with condition

SivaSatya — Sun, 14 Nov 2021 09:06:05 GMT

Thank You Kim... New Table is working fine.. Is it possible through new coumn/measure to get this in report?

Re: How to obtain the max value for several rows with the same id with condition

Jihwan_Kim — Sun, 14 Nov 2021 12:02:21 GMT

Weight Measure =

VAR currentID =

MAX ( Data[ID] )

VAR newtable_onorbeforetoday =

FILTER ( ALL ( Data ), Data[Date] <= TODAY () && Data[ID] = currentID )

VAR groupbytable =

GROUPBY (

newtable_onorbeforetoday,

Data[ID],

"@maxdate", MAXX ( CURRENTGROUP (), Data[Date] )

)

RETURN

IF (

HASONEVALUE ( Data[ID] ),

CALCULATE (

MAX ( Data[Weight] ),

KEEPFILTERS ( TREATAS ( groupbytable, Data[ID], Data[Date] ) )

)

Re: How to obtain the max value for several rows with the same id with condition

SivaSatya — Sun, 14 Nov 2021 14:34:52 GMT

Thank You Kim... It is working fine.