Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DataRodo
New Member

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

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.

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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.

v-kelly-msft_0-1612943119676.png

 

Best Regards,
Kelly

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

View solution in original post

6 REPLIES 6
v-kelly-msft
Community Support
Community Support

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.

v-kelly-msft_0-1612943119676.png

 

Best Regards,
Kelly

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

aj1973
Community Champion
Community Champion

Hi @DataRodo 

You can use COUNTBLANK(<column>) formula

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

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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

aj1973
Community Champion
Community Champion

@DataRodo 

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

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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

aj1973
Community Champion
Community Champion

@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

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.