Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
  • 1.  Checking if any cell is blank

    Posted Oct 23, 2021 11:15 AM
    Dear All,

    I have an Excel sheet with several columns which should be filled in by my staff. I then use Power Bi to create a report based on the data submitted. There are columns that contain numbers, text and dates as well. However, sometimes due to lack of attention, my staff sometimes forget to fill in all cells. Below is part of the table:


    As you can see, there one cell without data for the 2nd row, 5th row and 7th row.

    I would like to have a measure to detect if any cell is missing data from any column. The cells should not be replaced by values. I only want to know if data is missing by a measure.

    Is this possible?

    Thanks for your reply.


    ------------------------------
    Salim Hoolash
    Principal Engineer
    ------------------------------


  • 2.  RE: Checking if any cell is blank

    Top Contributor
    Posted Oct 25, 2021 08:35 PM
    Hi Salem:

    You can do a measures. COUNTROWS(Table) for total rows.

    Then use the COUNT Function for the individual columns. = COUNT(Table[Column in Question])

    Then subtract them. On a card you do some condition formatting if the variance is not 0 then give the result of the subtractions.

    I hope this helps.

    ------------------------------
    William Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------



  • 3.  RE: Checking if any cell is blank

    Top Contributor
    Posted Oct 25, 2021 08:40 PM
    Want to suggest COUNTA instead of COUNT because it will count whether it's a text or number, so you don't have to worry about data types...

    ------------------------------
    William Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------



  • 4.  RE: Checking if any cell is blank

    Top Contributor
    Posted Oct 27, 2021 10:25 AM
    Hi Salim:

    If you have the individual variances ( and if they only show a value if negative) you can do something like:

    Measure for Card = IF(([measure A] + [MeasureB] + [MeasureC])  < 0, " Missing Data")

    If you want to send me an example file I can answer it more definitely.

    Good luck.

    Bill

    ------------------------------
    William Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------



  • 5.  RE: Checking if any cell is blank

    Posted Oct 27, 2021 01:58 PM
    Hi William,

    THANKS!!! Works great. Exactly what I needed.

    Salim.

    ------------------------------
    Salim Hoolash
    Principal Engineer
    ------------------------------