Replacing the two last element of a list by a blank

[Anonymous]

Hello!

Like written in the subject, I would like to replace the two last elements of my measure by a blank. Do you know if it's possible? and if it is, how can I do it?

Thank you

Have a nice day!

[amitchandak]

@Anonymous , One way is to remove the last 2 items like

List.FirstN(MyList, List.Count(MyList -2))

may add another list to it

List.Combine(List.FirstN(MyList, List.Count(MyList -2)),{,})

List.Combine(List.FirstN(MyList, List.Count(MyList -2)),{0,0})

or

List.Combine(List.FirstN(MyList, List.Count(MyList -2)),{"",""})

[Anonymous]

Thank you @amitchandak ,

but the function didn't work the LIST. didn't got recognize by Power bi. Is it dax?

[v-kkf-msft]

Hi @Anonymous ,

This is a screenshot of my test.

The value of a measure is context dependent. What is your DAX? What kind of visual do you want to put the measure in, and what is its context?

Best Regards,
Winniz

[Anonymous]

Hello, Thank you for your answer

I got 12 measure with value in it. I would like for the first measure to keep all my element, the second one to replace by a blank the last element, for the third replace the two last element, the fourth replace the 3 last element...

By doing this I will have a kind of triangle in my report.

Initially I got a triangle of value and I would like to do the cumulative sum of this triangle. And When I do it I got no longer a triangle of value because it sum for all the column. And as in my previous triangle I got blank when my value is 0 so I cannot use an if condition. I put a screen of my problem maybe it will help to understant my issues.

Best regards

Thomas

[v-kkf-msft]

Hi @Anonymous ，

Try the following formula:

R1 = 
CALCULATE(
    [M0]+[M1],
    TOPN( 9, ALLSELECTED('Table'[MonthNo_year]), 'Table'[MonthNo_year], ASC )
)

R2 = 
CALCULATE(
    [M0]+[M1]+[M2],
    TOPN( 8, ALLSELECTED('Table'[MonthNo_year]), 'Table'[MonthNo_year], ASC )
)

R3 = 
CALCULATE(
    [M0]+[M1]+[M2]+[M3],
    TOPN( 7, ALLSELECTED('Table'[MonthNo_year]), 'Table'[MonthNo_year], ASC )
)

R4 = 
CALCULATE(
    [M0]+[M1]+[M2]+[M3]+[M4],
    TOPN( 6, ALLSELECTED('Table'[MonthNo_year]), 'Table'[MonthNo_year], ASC )
)

R5 = 
CALCULATE(
    [M0]+[M1]+[M2]+[M3]+[M4]+[M5],
    TOPN( 5, ALLSELECTED('Table'[MonthNo_year]), 'Table'[MonthNo_year], ASC )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

Thank you, the only thing is that you write topn(7...) or topn(9) but my number of line might change and if I enter 7 or 9 it wouldn't be a triangle anymore. There isn't a formula like topn(-1...) to only  delete the last one? Or maybe I could count the number of the measure M0 and then do (numberlineofMO-1) or minus 2...?

[v-kkf-msft · Accepted solution]

Hi @Anonymous ，

Try the following formula:

RowsNum = 
COUNTX(
    ALLSELECTED('Table'),
    'Table'[month_year]
)

 Then modify 9 -> [RowsNum]-1, 8 -> [RowsNum]-2...

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

Thank you its working!!