Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Creating a count blank measure taking into account multiple object at once

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?

Need Help

5 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Actually nevermind, I've converted all my columns to strings but it's just giving me 1 for all the answers, not sure why.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Have trouble to reproduce the scenario.

    This is the demo I created:

    I used countblank() and get this result:

    Measure = COUNTBLANK('Table'[value])

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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)