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

[gelsonwj]

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?

[v-zhangti · Accepted solution]

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.

[tamerj1]

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 )

[FreemanZ]

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)