topic DAX QUERY FOR FILL DOWN in DAX Commands and Tips

DAX QUERY FOR FILL DOWN

Anonymous — Thu, 23 Jun 2022 17:22:52 GMT

Hi Professionals..!!

Need help in understanding the dax for getting the last column data as measure, basically it is a fill down in dax.

Re: DAX QUERY FOR FILL DOWN

Jihwan_Kim — Thu, 23 Jun 2022 17:47:38 GMT

Hi,

Please check the below and the attached pbix file.

Those are for both creating a measure and a calculated column.

Desired measure: = VAR currentrows = MAX ( Data[Rows] ) VAR currentdate = MAX ( Data[Date] ) VAR startdate = MINX ( FILTER ( ALL ( Data ), Data[Rows] = currentrows && Data[NewMC] <> BLANK () ), Data[Date] ) VAR previousdate = MAXX ( FILTER ( ALL ( Data ), Data[Rows] = currentrows && Data[Date] <= currentdate && Data[NewMC] <> BLANK () ), Data[Date] ) VAR previousvalue = MAXX ( FILTER ( ALL ( Data ), Data[Rows] = currentrows && Data[Date] = previousdate ), Data[NewMC] ) RETURN IF ( HASONEVALUE ( Data[Rows] ), IF ( MAX ( Data[Date] ) = startdate, SUM ( Data[NewMC] ), previousvalue ) )

Desired CC = VAR currentrows = Data[Rows] VAR currentdate = Data[Date] VAR startdate = MINX ( FILTER ( Data, Data[Rows] = currentrows && Data[NewMC] <> BLANK () ), Data[Date] ) VAR previousdate = MAXX ( FILTER ( Data, Data[Rows] = currentrows && Data[Date] <= currentdate && Data[NewMC] <> BLANK () ), Data[Date] ) VAR previousvalue = MAXX ( FILTER ( Data, Data[Rows] = currentrows && Data[Date] = previousdate ), Data[NewMC] ) RETURN IF ( Data[Date] = startdate, Data[NewMC], previousvalue )

Re: DAX QUERY FOR FILL DOWN

Anonymous — Thu, 23 Jun 2022 17:50:45 GMT

Jihwan_Kim tamerj1 amitchandak SpartaBI

Re: DAX QUERY FOR FILL DOWN

Anonymous — Fri, 24 Jun 2022 13:23:26 GMT

https://drive.google.com/file/d/1CMFr0oRjq1rb8gdCas-5V5oEa_s606uW/view

Jihwan_Kim please help us with this data as we are unable to get the correct result

Re: DAX QUERY FOR FILL DOWN

Jihwan_Kim — Sat, 25 Jun 2022 06:47:13 GMT

Hi,

Your pbix file has a page-filter.

Please try the below and check the attached file.

Mydesire = VAR currentrows = MAX ( Mydata[Rows] ) VAR currentdate = MAX ( Mydata[Date] ) VAR startdate = MINX ( FILTER ( ALLSELECTED( Mydata ), Mydata[Rows] = currentrows && Mydata[NewMC] <> BLANK () ), Mydata[Date] ) VAR previousdate = MAXX ( FILTER ( ALLSELECTED ( Mydata ), Mydata[Rows] = currentrows && Mydata[Date] <= currentdate && Mydata[NewMC] <> BLANK () ), Mydata[Date] ) VAR previousvalue = MAXX ( FILTER ( ALLSELECTED ( Mydata ), Mydata[Rows] = currentrows && Mydata[Date] = previousdate ), Mydata[NewMC] ) RETURN IF ( HASONEVALUE ( Mydata[Rows] ), IF ( MAX ( Mydata[Date] ) = startdate, SUM ( Mydata[NewMC] ), previousvalue ) )