DAX Query for Count of Blank Cells

[Sajay_Prathap24]

Dears....I have a excel database and ther are some cells not filled. From Power BI, I need to know the count of those blank cells based on which column the blank cells is available. Please advise for the DAX query for this

For Eg: Column A = 4, Column D = 6

[tharunkumarRTK · Accepted solution]

@Sajay_Prathap24 

there are several ways to do this, I would suggest you to use the column statistics in DAX query view. It gives all the statistics in view. 
https://learn.microsoft.com/en-us/power-bi/transform-model/dax-query-view
Open DAX query view, on the right hand side you will find all the tables in your model. 

Right click on the table, click on Qucik queries -> click on Show column statistics. 

In the result tab, you will see a column caled Null count. 

You can also find blank or empty values from power query column profiling options. 'Column quality'

https://learn.microsoft.com/en-us/power-query/data-profiling-tools

However by default it will only profile top 1000 rows and the results would show in percentage. 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[rohit1991 · Accepted solution]

Option 1: Count Blank Cells for Each Column

Count_Blanks_ColumnA = COUNTROWS(FILTER(YourTable, ISBLANK(YourTable[ColumnA])))

Count_Blanks_ColumnD = COUNTROWS(FILTER(YourTable, ISBLANK(YourTable[ColumnD])))

Replace YourTable, ColumnA, and ColumnD with your actual table and column names.

Option 2: Count Blanks Dynamically for All Columns

Unpivot the columns in Power Query: Select your columns and use Unpivot Columns. Add a custom column: IsBlank = IF(ISBLANK([Value]), 1, 0). Group by the column name and sum IsBlank.

Option 3: Dynamic Measure for All Columns. To calculate blanks across all columns: |
Count_Blanks_All =

SUMX(ADDCOLUMNS(
VALUES(YourTable[ColumnName]),"BlankCount", COUNTROWS(FILTER(YourTable, ISBLANK(YourTable[ColumnName])))),[BlankCount])

Use in a table/matrix visual for column-wise blank counts.

[Idrissshatila · Accepted solution]

Hello @Sajay_Prathap24 ,

the way it will work for you is to do it per column

BlankCount_ColumnA = 
calculate(
COUNTROWS(Table), table[columnName] = blank)

[Anonymous]

Hi @Sajay_Prathap24 ,

Thanks for reaching out.

I reviewed this case and I think everyone's insights and workarounds are great.

Do you have any solutions provided by other users to solve the problem? Or you solved it by your own workaround, you are also welcome to share it.

If you still have doubts, please provide a more detailed description, preferably some virtual sample data, and the expected results.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kedar_Pande · Accepted solution]

@Sajay_Prathap24 

You can try:

CountBlanks_ColumnA = COUNTROWS(FILTER('Table', ISBLANK('Table'[ColumnA])))
CountBlanks_ColumnD = COUNTROWS(FILTER('Table', ISBLANK('Table'[ColumnD])))

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Idrissshatila · Accepted solution]

Hello @Sajay_Prathap24 ,

the way it will work for you is to do it per column

BlankCount_ColumnA = 
calculate(
COUNTROWS(Table), table[columnName] = blank)

[rohit1991 · Accepted solution]

Option 1: Count Blank Cells for Each Column

Count_Blanks_ColumnA = COUNTROWS(FILTER(YourTable, ISBLANK(YourTable[ColumnA])))

Count_Blanks_ColumnD = COUNTROWS(FILTER(YourTable, ISBLANK(YourTable[ColumnD])))

Replace YourTable, ColumnA, and ColumnD with your actual table and column names.

Option 2: Count Blanks Dynamically for All Columns

Unpivot the columns in Power Query: Select your columns and use Unpivot Columns. Add a custom column: IsBlank = IF(ISBLANK([Value]), 1, 0). Group by the column name and sum IsBlank.

Option 3: Dynamic Measure for All Columns. To calculate blanks across all columns: |
Count_Blanks_All =

SUMX(ADDCOLUMNS(
VALUES(YourTable[ColumnName]),"BlankCount", COUNTROWS(FILTER(YourTable, ISBLANK(YourTable[ColumnName])))),[BlankCount])

Use in a table/matrix visual for column-wise blank counts.

[tharunkumarRTK · Accepted solution]

@Sajay_Prathap24 

there are several ways to do this, I would suggest you to use the column statistics in DAX query view. It gives all the statistics in view. 
https://learn.microsoft.com/en-us/power-bi/transform-model/dax-query-view
Open DAX query view, on the right hand side you will find all the tables in your model. 

Right click on the table, click on Qucik queries -> click on Show column statistics. 

In the result tab, you will see a column caled Null count. 

You can also find blank or empty values from power query column profiling options. 'Column quality'

https://learn.microsoft.com/en-us/power-query/data-profiling-tools

However by default it will only profile top 1000 rows and the results would show in percentage. 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!