IF statement in measure gives blank

[Anonymous]

Dear all,

I have calculated a new rental price for a price increase exercise. 

As a last step I want to apply ROUND or ROUNDUP based on a slicer setting.

For this I have created a 'switch' (powered by a slicer).
Round or roundup:=IF(HASONEVALUE(Aufrundung[Aufrundung parameter]),VALUES(Aufrundung[Aufrundung parameter]),0)
This works and generates a 0 value for normal rounding and 1 for rounding up. If no choice is made, result is 0.

The price is calculated with this measure: 
PauschPreis neu:=SUMX('Existing prices','Existing prices'[Price]*([Price increase percentage]+1))
So far so good.

The thing where I stumble is to create a simple measure that uses the result from [Round or roundup] to either round this new price normally or round it up. Whatever I try, it does not give a result, other than blank.

Measure 1:=IF(([Round or roundup])=0,10,20)  (just using 10 and 20 to test this). The result is neither 10 or 20.

Any idea what I should do or what goes wrong? I could not find any hint in other posts.

Thx for a response, EJ

[Greg_Deckler · Accepted solution]

Huh, I also got it to work using your formulas as well. Still page 4.

[Stachu · Accepted solution]

my guess is your PowerPivot doesn't work properly, I'd try the following:
1) disabling the PowerPivot in Excel

2) closing all the Excel sessions (check in Task manager, kill the process if necessary)

3) enabling PowerPivot again, then checking the files

[Anonymous · Accepted solution]

Hi Greg,

It's not the first glitch I've noticed. Something similar happened to me last week.

I will follow your advice for future issues.

At least you've confirmed I am not crazy

Thank you for your help and hafve a nice weekend.

[Greg_Deckler]

So, the data for your slicer is either 0 or 1?

Are you sure it's not text?

[Anonymous]

Hello Greg,

My first thought too and I have checked that. I am led to believe it is numerical, since I can add decimals to the number.

Round or roundup:=IF(HASONEVALUE(Aufrundung[Aufrundung parameter]),VALUES(Aufrundung[Aufrundung parameter]),0)

[Anonymous]

And yes, it is 0 or 1

[Greg_Deckler]

OK, so on a whim, have you tried this?

Measure 1:=IF(([Round or roundup])="0",10,20) 

[Anonymous]

I did (before) and that gives an error indicating I am comparing valye types integer and text.
Hence my assumption I am using numerical values.

[Anonymous]

Hi Greg,

I am not familiar with PowerBI.
Found your formula and it is indeed identical to mine.

[Anonymous]

To be more precise, I use PowerPivot.
But I can see your measures.

[Anonymous]

Just checked 
Measure 1:=IF((1=0),10,20)
This should definetely give 20 as result. But it stays blank.

[Stachu · Accepted solution]

my guess is your PowerPivot doesn't work properly, I'd try the following:
1) disabling the PowerPivot in Excel

2) closing all the Excel sessions (check in Task manager, kill the process if necessary)

3) enabling PowerPivot again, then checking the files

[Anonymous · Accepted solution]

Hi Greg,

It's not the first glitch I've noticed. Something similar happened to me last week.

I will follow your advice for future issues.

At least you've confirmed I am not crazy

Thank you for your help and hafve a nice weekend.

[Stachu · Accepted solution]

the code you posted

Measure 1:=IF((1=0),10,20)

should return 20 in Excel - at least in 2013 & 2016, but I think it should be the same for 2010.

if it returns blank my guess PowerPivot/Excel instalation is somehow corrupted

[Anonymous]

Hello Stachu,

Thanks for your reply. Like Greg assumed, something did not work properly.
Very frustrating, I can tell you.

I will see that I have it installed again.

BR, EJ

[Anonymous]

Hi Greg,

I have thrown in a couple of parentheses and this seems to have a positive effect.

Measure 1:=IF(([Round or roundup]=0),(10),(20)) now results in expected results.

No clue why it did not work before. Do numbers (and formulas) always require extra parentheses?

[Greg_Deckler]

Weird, I don't do much in PowerPivot so can't really speak to the differences between it and Power BI Desktop.

[Greg_Deckler]

I got this to work using a What If parameter, see page 4 of attached file.

[Greg_Deckler · Accepted solution]

Huh, I also got it to work using your formulas as well. Still page 4.