Is there a way to count values that are headers?

[GON76]

I have a matrix table that is using headers as rows.  I would like to be able to count specific values in the rows. As you can see in the screenshot, the table is showing 3 "Not Compliant" and 3 "Incomplete, but the card counting these field are not accurate.  The Not Compliant and Learning Path Completion Status are both caluclated columns.  Is there a way to do this?

[v-tsaipranay]

Hi @GON76 ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @GON76 ,

I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further.

Thank you.

[v-tsaipranay]

Hi @GON76 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-tsaipranay]

Hi @GON76 ,

Thank you for reaching out to the Microsoft fabric community forum. Also thank you @wc123 and @Ashish_Mathur   for your inputs.

The issue happens because Not Compliant and Learning Path Completion Status are calculated columns that count rows, not unique users. If a user is enrolled in multiple courses, this results in higher counts. To resolve this, use measures with DISTINCTCOUNT, such as:

Not Compliant Users =
CALCULATE(
    DISTINCTCOUNT('Sample_Compliance_Data'[USERNAME]),
    'Sample_Compliance_Data'[Not Compliant] = "Not Compliant"
)

Incomplete Users =
CALCULATE(
    DISTINCTCOUNT('Sample_Compliance_Data'[USERNAME]),
    'Sample_Compliance_Data'[Learning Path Completion Status] = "Incomplete"
)

This approach ensures each user is counted only once, so the card visuals will match your matrix. I’ve included a sample PBIX with data to show the expected results.

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

Thankyou.

[Ashish_Mathur]

Hi,

Try this measure

Measure = calculate(distinctcount(Data[Username]),Data[Not compliant]="Not compliant")

If it does not work, share the download link of the PBI file.  Show the problem there clearly.

[GON76]

Hi, unfortunately, I can't share the PBIX file since it's only on our intranet.  Here are some screenshots that may be helpful:

This is a screenshot of the calculated columns that I'm trying to count:

Here's a screenshot of the matrix table with all of the headers expanded:

Let me know if there's anything else you would like to see.  Thanks in advance!

[wc123]

You should create a new measure using DAX. The DAX should be something along the lines of this:

YourMeasureName = COUNTROWS(FILTER(TableName, TableName[ColumnName] = "YourValue"))

[GON76]

Getting closer, but it seems like it's counting each value 2 times. Is there anything else I should check?

[wc123]

I see better now. You need it to count also based on the filters applied to the visual. what should work is instead of using COUNTROWS you can use something along the lines of this.

FilteredRowCount =

CALCULATE(

    COUNTROWS(TableName),

    TableName[ColumnName] = "YourValue"

)

[GON76]

Unfortunately, that didn't work. I'm still seeing a count of 6 when it should be 3.  Does it matter if these values are headers?