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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
brendaivett
Advocate II
Advocate II

How to not count the null/empty value in visualizations

Hi,

 

I just noticed that in my visualizations, when I use the Count Distinct to display values, it's actually counting one more for the Null/Empty values. I tried making the values "" or Null, from the reporting side, and it doesn't matter, the "value" is still counted.

 

I know it's just one more value, but that makes it not 100% accurate to what my listing is backing up.

 

Is there a way to force the Count to not count the Null/Empty value in the visualizations?

 

Thanks in advance!

 

Brenda

1 ACCEPTED SOLUTION

@brendaivett

 

A fast way is add the column in visual level filter and unselected Blank.

 

NotBlank.png




Lima - Peru

View solution in original post

5 REPLIES 5
vanessafvg
Super User
Super User

i think you need to use this function

 

COUNTAX Function (DAX) The COUNTAX function counts nonblank results when evaluating the result of an expression over a table. That is, it works just like the COUNTA function, but is used to iterate through the rows in a table and count rows where the specified expressions results in a nonblank result.

 

https://msdn.microsoft.com/en-us/library/ee634219.aspx?f=255&MSPPError=-2147217396





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hi,

 

I used DISTINCTCOUNT for a measure, but in this case it's for showing the count number of records in a chart or table, so that the user can see the total count and if they want to see the records that add to it, just use the "see records" option.

 

Count (Distinct) In Visualization.JPG

 

In the case I'm testing, I have 1608 different values among several records, and there are some Null values, and the count is showing 1609, so I'm assuming is counting the "distinct empty value".

 

Thanks!

 

Brenda

 

@brendaivett

 

A fast way is add the column in visual level filter and unselected Blank.

 

NotBlank.png




Lima - Peru

Hi Vvelarde

 

I am using calculated measure and this filtering is not working .

 

Could you please help ?

 

Sajid

Anonymous
Not applicable

i'm in the same situation of this post and it seems there is no solution yet

 

basically the COUNT DISTINCT in the graphs sums also the BLANK even if i was able to subtract them from the table!

 

Capture count distinct.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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