cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

1 ACCEPTED SOLUTION
Super User

Sure, can you please try this updated approach:

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

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
5 REPLIES 5
Super User

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.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Frequent Visitor

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.

Super User

Sure, can you please try this updated approach:

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

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Frequent Visitor

Thank you.  Worked perfect.  Appreciate your help.

Super User

Anytime, I'm happy to help - let me know if you might have any further questions.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors