Forum Discussion

Alaska1's avatar
Alaska1
Frequent Visitor
2 years ago
Solved

Formula including blanks rows

I have a data field in Power BI on a rating scale from 0-10.  I have created a column that will mark anything <9 as a 1 and anything greater than 9 with a 0.

 

Measure = If(Table1[Recommend Score]))<9,1,0)

 

I am having a problem eliminating blank fields.  The formula is giving the blank rows a 1.  I need to do calculations based on the 1 in the field.  If it is including the blanks as a 1, my calculations will not be correct.  

 

Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    Sure, can you please try this updated approach:

    Measure = 
IF(
    ISBLANK(Table1[Recommend Score]),
    BLANK(),
    IF(Table1[Recommend Score] < 9, 1, 0)
)

5 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    Hello Alaska1,

     

    Can you please try this approach:

    Measure = 
IF(
    NOT(ISBLANK(Table1[Recommend Score])) && Table1[Recommend Score] < 9,
    1,
    0
)

    Let me know if you might require any further assistance.

    • Alaska1's avatar
      Alaska1
      Frequent Visitor
      2 years ago

      Thank you for  your quick reply. 

       

      The formula worked as far as putting <9 as a 1 and >9 as 0.  For blank rows it put a zero in the column.  Is there any way to not have the blank rows have a zero? Just have them blank.   If not, I can work around it.

       

      Thank you again for your help.

       

      • Sahir_Maharaj's avatar
        Sahir_Maharaj
        Super User
        2 years ago

        Sure, can you please try this updated approach:

        Measure = 
IF(
    ISBLANK(Table1[Recommend Score]),
    BLANK(),
    IF(Table1[Recommend Score] < 9, 1, 0)
)