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!

[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.

[wardy912]

Hi @GON76 

 Thanks, it makes complete sense. I've used your calculated column and it gives the same results as your static values in the above table.

 I'm not sure how else I can help, as I have demonstrated a working solution.

What exactly doesn't work? Can you share screenshots and a detailed run through of what you have done and where it is wrong? thanks

[GON76]

Hi wardy912, here's a screenshot.  I have a filter on the division just to make the list more manageable.  As you can see, 15 Not Compliants are being counted when only 2 should be counted (there's the same issue with the Not Completed count, which is probably the same issue). Thanks again.

[jithin-mathew]

Hi @GON76 

Based on your data I was able to find the count of non complaint entries as well as the number of students that are non complaint as well and I have verified that the counts are mathcing with the data provided. (screenshot)

But I want to check with you if this is your actual question or the calcuation of the status as Non Complaint/Complaint itself ?

✎ᝰ. ------------

Jithin Mathew

If this post helped, give it a kudos! 👍

✅ Mark as solution to help others find it faster.

#MicrosoftFabricCommunity

[Ashish_Mathur]

Hi,

Based on the sample data that you have shared, show the expected result.

[wardy912]

Yes thank you, I'll get an answer over to you tomorrow

[ryan_mayu]

@GON76 

you can try to use the measure

= calculate(distinctcount(firstcolumn), filter(Table ,[Compliance Status]="Not Complaint"))

if this does not work, pls provide the sample data 

[GON76]

Hi Ryan, that didn't work... it return a count of 1.  Unfortunately, I can't share the file since it's only on our intranet.  The formula is somehow counting items that are not being shown in the table.  Any thoughts?

Leanring Path Compliance = calculate(DISTINCTCOUNT(V_ENROLLMENTS[Compliance Status]), filter(V_ENROLLMENTS ,[Compliance Status]="Not Compliant"))

[wardy912]

Hi @GON76 

 Your measure is counting every row in the source table that is 'Not Compliant'. Try adding the compliance status column into the matrix again, except this time show as a count. This will show the true numbers that 'Not Compliant' appears for the row item.

[GON76]

That's what I had originally. Am I missing something? It seems like it'a counting values that are not being filtered.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[GON76]

I can't unfortunately.  The file is only on our intranet.

[MasonMA]

Hello @GON76 

On your last picture with implicit measure and card visual level filter, Your card filters don't look aligned with page level filter. 

Even though the visual-level filter is "Not Compliant", the card is not inheriting the row-level filter context from the table visual.

You may try applying filters at the page or report level, so they affect the card as well as table.

Also when using the page/report level filter, set 'Compliance Status' Is 'Not Compliant' specifically rather than 'Is not' 'Compliant' because when there are more than two possible values in the Compliance Status column (e.g., "Compliant", "Not Compliant", "" [blank], "Pending", etc.). They will all be counted as "Not Compliant". 

Hope this works:) 

[GON76]

Hi, I tried that and it still didn't work.  The number in the card did not change.

[jithin-mathew]

Hi @GON76 

Why don't you try the following DAX instead!

CALCULATE(
    COUNT('Table'[Column]),
    'Table'[Column] = "Not Complaint"
)

✎ᝰ. ------------

Jithin Mathew

If this post helped, give it a kudos! 👍

✔️Mark as solution to help others find it faster.

#MicrosoftFabricCommunity 

[GON76]

I tried that, and the count didn't change on the visual. 

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

Does is make a difference that Compliance Status is a measure?

[jithin-mathew]

Hi @GON76 

From the screenshot what I can deduce is it's a calculated column rather than a measure. This couldn't possibly affect the above calculation.

I tried to make a similar situation from my desktop and it is giving the result correctly, just by draging the complaint status column and summarizing it as count. If you can replicate your report with some non sensitive data, then we can have a look at it.

✎ᝰ. ------------

Jithin Mathew

If this post helped, give it a kudos! 👍

✅ Mark as solution to help others find it faster.

#MicrosoftFabricCommunity

[GON76]

See what I did in the screenshot.  It's counting items that aren't on the table.  Any reason why that could happen?

[d_m_LNK]

I am curios about what's in the table/data model since you keep getting the results.  It seems to be ignoring the filter context and counting the enrollments in the table.  A couple things to try to try to make sure the filter context is applied:

CALCULATE(
COUNT('V_ENROLLMENTS'[Compliance Status]),

,KEEPFILTERS('V_Enrollments')
,'V_ENROLLMENTS'[Compliance Status] = "Not Compliant"
)

OR

CALCULATE(
COUNT('V_ENROLLMENTS'[Compliance Status]),

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

[GON76]

Hi, unfortunately these didn't work. I'm not sure if it makes a difference, but the course titles are actually headers put into columns.  The Completion Date is under "Values". Do you see anything that may be wrong?

[GON76]

What's the best way to send the PBIX file?  I don't see an icon to send an attachment other than images.