Forum Discussion

powerbi_bi's avatar
powerbi_bi
Frequent Visitor
9 years ago

IF CONDITION or ????????

Hi, I have a measure (Measure3) which does this Measure3%:=IF([Measure1]=0, BLANK(),[Measure2]/[Measure1])   Measure1 Measure2 Measure3% 0.5           0.5           1 ...
Need Help
radpir's avatar
radpir
Resolver II
9 years ago

hi,

 

try this:

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

 

radpir

  • powerbi_bi's avatar
    powerbi_bi
    Frequent Visitor
    9 years ago

    Hi radpir

    Thanks for the quick response but its not working

     

    • radpir's avatar
      radpir
      Resolver II
      9 years ago

      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