IF CONDITION or ????????

[powerbi_bi]

Hi,

I have a measure (Measure3) which does this

Measure3%:=IF([Measure1]=0, BLANK(),[Measure2]/[Measure1])

Measure1	Measure2	Measure3%
0.5
0.5
1

I want Measure3% to do this

Measure1	Measure2	Measure3%	Measure3% DESIRED
0.5			0.00%
			N/A
0.5			0.00%
			N/A
1			0.00%

Please let me know how I can make Measure3% to also show

0%  --> If Measure1 has a value and Measure2 is BLANK

N/A --> If Measure1 and Measure2  is BLANK

using my current DAX formula for Measure3%

Thanks

[radpir]

hi,

try this:

Measure3% :=
IF (
AND ( ISBLANK ( [Measure1] ), ISBLANK ( [Measure2] ) ),
"N/A",
DIVIDE ( [Measure2], [Measure1], BLANK () )
)

radpir

[powerbi_bi]

Hi radpir

Thanks for the quick response but its not working

[radpir]

sorry, my bad.

both sides of the IF function needs to return the same data type.

N/A is a text value and DIVIDE returns a numeric value hence the reason why this formula doesn't work.

this one should work:

Measure3 = IF (
AND ( ISBLANK ( [Measure1] ), ISBLANK ( [Measure2] ) ),
"N/A",
FORMAT(DIVIDE ( [Measure2], [Measure1], BLANK () ), "0.00")
)

I wrapped the DIVIDE function in the FORMAT function which returns a text value.

The one big drawback of this approach is that this measure is not returning text values even though they may appear as numeric values. They can't be summed, averaged, etc.

radpir