Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hey there. I am new to PowerBI and have been trying to create a function which would count the blanks in a range of columns to see how many data points are missing for each object. We are dealing with vendors and ask for information such as address, phone number, and email. The only way I found to do this was by creating a different COUNTBLANK measure for each object. Is there a way I can create a single measure instead?
Hi @Anonymous ,
Have trouble to reproduce the scenario.
This is the demo I created:
I used countblank() and get this result:
I'm not sure why you have to create multiple measures.
I guess I must missed some points.
Please share some sample data and expected result.
Best Regards,
Jay
There are "multiple measures" within a column rather than an all-encompassing measure covering the table for two reasons:
1) I couldn't find an equation covering the whole table which was able to tell me for each row whether there was a blank in it or not
2) I am only selecting some objects within the table and I want it to be easy for people to choose which objects to select when counting the blank row.
I can't share the data as it's sensitive. I did find an equation that works though similar to @Nathaniel_C's. I think the reason his equation didn't work is because some of my objects were strings and some whole numbers.
Blank Rows = IF(ISBLANK('List Of Approved Vendors'[ADDRESS]) || ISBLANK('List Of Approved Vendors'[COUNTRY]) || ISBLANK('List Of Approved Vendors'[PO BOX]) || ISBLANK('List Of Approved Vendors'[CR NO.]) || ISBLANK('List Of Approved Vendors'[City]) || ISBLANK('List Of Approved Vendors'[Country Code]) || ISBLANK('List Of Approved Vendors'[TELEPHONE #]) || ISBLANK('List Of Approved Vendors'[FAX #]) || ISBLANK('List Of Approved Vendors'[EMAIL]) || ISBLANK('List Of Approved Vendors'[CONTACT PERSON]) || ISBLANK('List Of Approved Vendors'[TELEPHONE NO.]) || ISBLANK('List Of Approved Vendors'[MOBILE NO.]), 1, 0)
Hi @Anonymous ,
Please try this and then you may sum the values:
Countblank if any are blank = IF(MAX(Tableblank[Address])="" || MAX(Tableblank[Email]) =""|| MAX(Tableblank[Phone])="",1,0)
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Wow thank you, this really helped! As a follow up quastion, what do I do if some of the values are integers and some are strings?
Actually nevermind, I've converted all my columns to strings but it's just giving me 1 for all the answers, not sure why.