topic Re: store only the first value of a filter and fill the rest with 0 in DAX Commands and Tips

store only the first value of a filter and fill the rest with 0

gelsonwj — Mon, 26 Dec 2022 13:57:00 GMT

I would like to create a new column that stores only the first average value of a date and fills the rest with 0. How do I do this?

Re: store only the first value of a filter and fill the rest with 0

FreemanZ — Mon, 26 Dec 2022 14:32:43 GMT

hi gelsonwj

try to add a column with this:

Average2 = VAR _average = [Average] VAR _MaxID = MAXX( FILTER(TableName, TableName[Average] = _average ), TableName[id] ) RETURN IF( [id] = _MaxID, _average, 0)

Re: store only the first value of a filter and fill the rest with 0

tamerj1 — Tue, 27 Dec 2022 07:06:48 GMT

Hi gelsonwj

please try

First Average = VAR CurrentID = 'Table'[id] VAR DateFarmIDTable = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Farm_id], 'Table'[Date_short] ) ) VAR FirstRecord = TOPN ( 1, DateFarmIDTable, 'Table'[id] ) VAR MaxID = MAXX ( FirstRecord, 'Table'[id] ) VAR FirstAverage = MAXX ( FirstRecord, 'Table'[Average] ) RETURN IF ( 'Table'[id] = MaxID, FirstAverage, 0 )

Re: store only the first value of a filter and fill the rest with 0

v-zhangti — Tue, 27 Dec 2022 05:23:09 GMT

Hi, gelsonwj

You need to add an index column to the power query first.

Column:

Average = Var _Aver=CALCULATE(AVERAGE('Table'[Quantity]),ALLEXCEPT('Table','Table'[Date_short])) Var _minindex=CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Date_short])) Return IF([Index]=_minindex,_Aver,0)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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