Count the blank cells of a column in a shown Data Table

[DataRodo]

Hello everyone,

I'm working in a Power BI dashboard, I'm pulling the information from an Analysis Services Cube,  I'm using the data of the selected fields into a Data Table visualization, until this, It's working perfectly. The request is that I need to show in the value of a CARD the total or the COUNT of cells that blank of a column that is being shown.

I have tried different Measures formulas but no luck until now. IWould appreciate a lot your help.

Thank you.

[v-kelly-msft · Accepted solution]

Hi @DataRodo ,

If the field is a text type,using below dax expression:

Measure = CALCULATE(COUNTROWS('DimGeography'),FILTER(ALL(DimGeography),'DimGeography'[City]=BLANK()))+0

If it's a whole number format,try below:

Measure = CALCULATE(COUNTROWS('DimGeography'),FILTER(ALL(DimGeography),Not(ISNUMBER('DimGeography'[City]))))+0

 After checking,the above measures work fine here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[aj1973]

Hi @DataRodo 

You can use COUNTBLANK(<column>) formula

https://docs.microsoft.com/en-us/dax/countblank-function-dax

[DataRodo]

Actually that's the one that I was trring to use but for some reason the result is always: [ 1 ] or sometimes <blank> 

[aj1973]

@DataRodo 

How is your Model connecting to your Cubes? Live connection?

[DataRodo]

I think yes. The connection is happening to a connection to the server.

[aj1973]

@DataRodo 

I am afraid it is the problem. check this out

https://community.powerbi.com/t5/Desktop/Can-t-create-report-level-measure-on-Power-BI-Desktop-live/...

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-analysis-services-tabular-data