Count of values not accurate

[GON76]

I'm trying to set up a card visual that counts a particular value on my matrix table.  As you can see from the screenshot, the visual is showing 32 values that equal "Not Compliant" but the table is showing only 5.  I'm using the following formula: 

COUNTROWS ( FILTER ( Table, Table [Column] = "Value" ) )

There is a filter on this view, but shouldn't the card only be counting 5 "Not Compliant" values?

Thanks!

[v-venuppu]

Hi @GON76 ,

Sorry for the delay in Response..!!

Thank you for reaching out to Microsoft Fabric Community.

Thank you @d_m_LNK @wardy912 @jithin-mathew @Ashish_Mathur  for the prompt response.

Please go through the attached PBIX file for your reference.

If the issue still persists, I would request you to share the sample PBIX file, so that it will be helpful for us to solve the issue more effectively.

[wardy912]

Hi @GON76 

 Sorry, mines still working. As long as you use the measure rather than the calculated column in your card visual, and you replace the measure information with your table name and column you're using it will work:

[GON76]

Would it make a difference if the course titles are headers and the completion date are values?

[wardy912]

No, but check something else please.

Check your slicer interactions with the card visual

Go to format, edit interactions, select the slicer and make sure this isn't selected (dark grey circle icon)

Deselected so that slicer is effective

[GON76]

This is what I'm seeing.

[wardy912]

Hi @GON76 

 Can you take a step back please and add the column to a table visual to show the actual count of 'not compliant', the matrix visual may be combining results so hiding the real number. Once that's confirmed you will be able to add other columns to find out what's causing the issue. Maybe it's the categories. At that point you can edit your measure to count categories that contain the value 'not compliant' rather than counting every row that contains 'not compliant'.

Another method would be to create a visual calculation that counts rows containing 'not compliant' to only count the rows in the matrix rather than the source data table.

[GON76]

Here's a screeshot.  The formula for the Count of Compliance should mark all compliance dates older than 3 years "Not Compliant".  It's a calculated column if that makes any difference.

[d_m_LNK]

Thanks! One last thing to check, are there any additional filters on the Matrix visual itself that aren't applied to the page as a whole?

[GON76]

Yes, there's one that filters out certain characters for the USERNAME.  Should I apply it to the page instead?

[d_m_LNK]

Yes please give that a try as we continue to see them filtered differently.

[GON76]

I just tried that, and no luck.

[d_m_LNK]

Based on this screenshot -- you are using the calculated column as the value for the visual with an implicit measure doing the count.  You need to create a measure with the code given in previous threads and make sure the card visual is using the measure and not the calculated column as the value.

[GON76]

So I deleted the measure I was using, and recreated it with one of the formulas someone suggested. It's still not working: 

Not Compliant = CALCULATE( COUNT('V_ENROLLMENTS'[Compliance Status]) ,ALLSELECTED('V_Enrollments') ,'V_ENROLLMENTS'[Compliance Status] = "Not Compliant"

At this point, I might just remove the cards.  Sorry it's so frustrating.

[d_m_LNK]

Also change the measure back to this code:

Not Compliant = CALCULATE( COUNTROWS('V_ENROLLMENTS') ,'V_ENROLLMENTS'[Compliance Status] = "Not Compliant"

[d_m_LNK]

Thanks for trying that, can you send another screenshot like above with the card visual selected showing and the measure definition?

[GON76]

Sure, let me know if this is what you're asking for.

[wardy912]

There are a couple of stand out points.

First, you now have 8 instances of 'not compliant'. Second, you have courses that are showing as 'not compliant' and 'compliant', so you might need to edit your calculated column to fix that as well.

We will need a dataset to fix this, but that doesn't need to be a full download. Add all of the columns to a table visual and click the 3 dots on the top right on the visual, export as a csv. Once we have that table it will be quick to fix, thanks

[GON76]

I copied a portion of the data table and removed the PII data.  So what I was trying to do was show the Learning Path Completion status as Complete if the student had a completion date for all of the courses.  The Learning Path Compliance is supposed to show "Not Compliant" if any of their completion dates were before 3 years from today. I wasn't sure how to upload the CSV file itself.  Is this what you need?  Thanks for your help.

USERNAME	FD_DIV_DESCR	FD_REGION	Compliance Status	Learning Path Completion Status	Course Title	Completion Date
AABBA	DOF	WAS	Not Compliant	Complete	Performance Management Program	5/10/2022 0:00
AAGAPOV	RMS	NY	Not Compliant	Complete	Performance Management Program	3/29/2022 0:00
AALLAL	DRR	WAS	Not Compliant	Complete	Corrective Actions: Managing Conduct & Performance	3/4/2023 12:00
AALLAL	DRR	WAS	Not Compliant	Complete	Foundations of Supervisory Leadership Part I	6/9/2023 0:00
AALLAL	DRR	WAS	Not Compliant	Complete	Foundations of Supervisory Leadership Part II	9/15/2023 0:00
AALLAL	DRR	WAS	Not Compliant	Complete	Performance Management Program	11/1/2022 0:00
AAMICANGIOLI	RMS	NY	Not Compliant	Complete	Corrective Actions: Managing Conduct & Performance	11/24/2021 0:00
AAMICANGIOLI	RMS	NY	Not Compliant	Complete	Foundations of Supervisory Leadership Part II	3/11/2022 0:00
AAMONTGOMERY	DCP	WAS	Not Compliant	Complete	Performance Management Program	2/2/2024 12:01
AAPPULINGAM	OCISO	WAS	Compliant	Complete	Delivering Employee Feedback	7/16/2025 8:12
AAPPULINGAM	OCISO	WAS	Not Compliant	Complete	Corrective Actions: Managing Conduct & Performance	6/18/2024 17:59
AAPPULINGAM	OCISO	WAS	Not Compliant	Complete	Foundations of Supervisory Leadership Part I	6/28/2024 13:15
AARMOORE	LEGAL	DAL	Not Compliant	Complete	Corrective Actions: Managing Conduct & Performance	1/6/2023 15:52
AARMOORE	LEGAL	DAL	Not Compliant	Complete	Foundations of Supervisory Leadership Part I	2/3/2023 0:00
AARMOORE	LEGAL	DAL	Not Compliant	Complete	Foundations of Supervisory Leadership Part II	4/28/2023 0:00
AARMOORE	LEGAL	DAL	Not Compliant	Complete	Performance Management Program	4/21/2025 12:23
AASONYE	DOA	WAS	Not Compliant	Complete	Corrective Actions: Managing Conduct & Performance	4/22/2019 0:00
AASONYE	DOA	WAS	Not Compliant	Complete	Foundations of Supervisory Leadership Part I	5/3/2019 0:00
AASONYE	DOA	WAS	Not Compliant	Complete	Foundations of Supervisory Leadership Part II	8/2/2019 0:00
ABABRAHAM	DOF	WAS	Not Compliant	Complete	Performance Management Program	1/2/2024 8:25
ABARBES	DCP	WAS	Not Compliant	Complete	Performance Management Program	3/6/2023 9:48
ABBOWMAN	DCP	KC	Not Compliant	Complete	Performance Management Program	4/9/2024 12:42
ABICAK	OCISO	WAS	Not Compliant	Complete	Performance Management Program	3/29/2022 0:00
ABIRON	DCP	NY	Not Compliant	Complete	Performance Management Program	7/31/2023 16:01
ABLANCHARD	DOA	WAS	Not Compliant	Complete	Performance Management Program	3/28/2022 0:00
ABONDI	RMS	NY	Not Compliant	Complete	Performance Management Program	3/28/2022 0:00

[wardy912]

Hi @GON76 

 I am now using the data you have provided.

As you can see, the matrix visual (by design) groups the results by course title (or whatever column you use for the rows), whereas the card visual shows the count of 'not compliant' in the data source. This is all correct.

What you're trying to achieve, is a count of 'not compliant' as it relates to the rows in your matrix, therefore you need to add that logic to your calculation. Add a measure (not a calculated column as this will iterate across all rows) as follows:

NonCompliantMeasure = 
CALCULATE(
    DISTINCTCOUNT('Compliance'[Course Title]),
    'Compliance'[Compliance Status] = "Not Compliant"
)

This results in:

In the measure, swap out the 'course title' for whatever column you are using in your matrix 'rows'

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

[GON76]

Hi, thanks for taking the time to look at this.  Unfortunately, it didn't work.  Maybe it would help if I provided the formula for the calculated column.

Compliance Status Column:

Compliance Status =

IF(

    V_ENROLLMENTS[EXITDATE].[Date] < EDATE(TODAY(), -3),

    "Not Compliant",

    "Compliant"

)

The EXITDATE is the date of the completion of the course.  What I'm trying to show is if any course's EXITDATE is older than 3 years, "Not Complaint" would show.  Does that make sense?

EXITDATE is on the a table called V_ENROLLMENTS

Thanks again for your help.