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])

 

Measure1Measure2Measure3%
0.5  
   
0.5  
   
1  

 

I want Measure3% to do this

Measure1Measure2Measure3%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

Need Help

3 Replies

  • 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