Wrong formula in calculated column

[gianmarco]

Dear All,

I have the following calculated table:

This works as long as I have at least a value which is not only 0 in "Cashflow[Scenario]".

In fact, in this example, I have values 0 and 1 as values in "Cashflow[Scenario]".

Problem is this formula doesn't return anything if I have only value 0 in "Cashflow[Scenario]".

In this case I would like the formula to return the following:

Value	Scenario
0	1

Can you suggest me something?
Thank you a lot to the whole community

gianmarco

[Anonymous · Accepted solution]

Hi @gianmarco ,

Please have a try.

Max Scenario = MAX(Cashflow[Scenario]) // create measure for max scenario

Calculated Table = 
FILTER( // filter out blank or zero values
    ADDCOLUMNS( // add new column for scenario
        Cashflow,
        "Scenario", IF(Cashflow[Scenario] = 0, [Max Scenario], Cashflow[Scenario]) // replace 0 with max scenario
    ),
    NOT(ISBLANK(Cashflow[Value])) && Cashflow[Value] <> 0
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[lbendlin]

You are filtering too soon. Do the summarize first and then check your scenarios.