Dax for matrix table

[Kay_Kalu]

I have this table image below 

Lookin to create a matrix table below 

Total Checking Parent Count = Distinct count of unique checking_parent/JIRA_ID where the checking parent matches the "Checking Parent" column on this table

Total SQL Feedback = ( (Distinct count of unique checking_parent/JIRA_ID/feedback category = "SQL" /Item_ref where the checking parent matches the "Checking Parent" column on this table) 

Total SQL No Feedback = ( (Distinct count of unique checking_parent/JIRA_ID where the checking parent matches the "Checking Parent" column on this table) * (SQL Weighting for the "Checking Parent" colmun in the "Task Weighting" tab) ) - Minus cases from the "Total Feedback" column

If I could be helped with DAX for this calculation it will help.  

[bhanu_gautam · Accepted solution]

@Kay_Kalu , Use

dax
TotalCheckingParentCount =
CALCULATE(
DISTINCTCOUNT('YourTable'[JIRA_ID]),
'YourTable'[checking_parent] = 'YourTable'[Checking Parent]
)

dax
TotalSQLFeedback =
CALCULATE(
DISTINCTCOUNT('YourTable'[Item_ref]),
'YourTable'[checking_parent] = 'YourTable'[Checking Parent],
'YourTable'[feedback_category] = "SQL"
)

dax
TotalSQLNoFeedback =
VAR TotalCheckingParent =
CALCULATE(
DISTINCTCOUNT('YourTable'[JIRA_ID]),
'YourTable'[checking_parent] = 'YourTable'[Checking Parent]
)
VAR SQLWeighting =
LOOKUPVALUE(
'TaskWeighting'[SQLWeighting],
'TaskWeighting'[Checking Parent], 'YourTable'[Checking Parent]
)
VAR TotalFeedback =
[TotalSQLFeedback]
RETURN
(TotalCheckingParent * SQLWeighting) - TotalFeedback

dax
SQLFailRate =
DIVIDE(
[TotalSQLFeedback],
[TotalSQLNoFeedback],
0
)

[Kay_Kalu]

please could you help with this part

Total SQL No Feedback = ( (Distinct count of unique checking_parent/JIRA_ID where the checking parent matches the "Checking Parent" column on this table) * (SQL Weighting for the "Checking Parent" colmun in the "Task Weighting" tab) ) - Minus cases from the "Total Feedback" column

If I could be helped with DAX for this calculation it will help.

Checking Parent	SQL Weighting	Business Weighting	Process Weighting
Population Review Check	3	2	7
Data Fix Driver File Checking	1	2	3
P&C Request Checking	0	1	4
CS/Suspense Handover Checking	1	0	5
Bespoke Datafix Spec Checking	0	0	4
Sweep Docs Checking	2	3	3
Review Signoff	2	0	6
Calc Flag Review Signoff	2	0	5
Totals	11	8	37

Attached is the Task Weighting table. so it uses the weight "SQL Weighting" when the iteam_ref is SQL and so on. the checking_parent will be row filtered 

[Kay_Kalu]

Thanks This works 👍

[alish_b]

Awesome @Kay_Kalu !

Happy to help!

[bhanu_gautam · Accepted solution]

@Kay_Kalu , Use

dax
TotalCheckingParentCount =
CALCULATE(
DISTINCTCOUNT('YourTable'[JIRA_ID]),
'YourTable'[checking_parent] = 'YourTable'[Checking Parent]
)

dax
TotalSQLFeedback =
CALCULATE(
DISTINCTCOUNT('YourTable'[Item_ref]),
'YourTable'[checking_parent] = 'YourTable'[Checking Parent],
'YourTable'[feedback_category] = "SQL"
)

dax
TotalSQLNoFeedback =
VAR TotalCheckingParent =
CALCULATE(
DISTINCTCOUNT('YourTable'[JIRA_ID]),
'YourTable'[checking_parent] = 'YourTable'[Checking Parent]
)
VAR SQLWeighting =
LOOKUPVALUE(
'TaskWeighting'[SQLWeighting],
'TaskWeighting'[Checking Parent], 'YourTable'[Checking Parent]
)
VAR TotalFeedback =
[TotalSQLFeedback]
RETURN
(TotalCheckingParent * SQLWeighting) - TotalFeedback

dax
SQLFailRate =
DIVIDE(
[TotalSQLFeedback],
[TotalSQLNoFeedback],
0
)

[Kay_Kalu]

Thanks TotalCheckingParentCount  worked fine but I guess for the TotalSQLFeedback i haven't explained it well so below is a sample explained. thanks

jira_id	item_ref	comment_date	checking_parent	feedback category
PPDQ-9506	PR1	18/07/2025 16:52	Population Review Check	SQL
PPDQ-9387	PR1	01/07/2025 16:17	Population Review Check	SQL
PPDQ-9387	PR2	01/07/2025 16:17	Population Review Check	SQL
PPDQ-9350	PR1	16/06/2025 17:24	Population Review Check	SQL
PPDQ-9225	PR1	20/05/2025 17:21	Population Review Check	SQL
PPDQ-9225	PR1	01/07/2025 13:54	Population Review Check	SQL

From the table above I have filtered the feedback category = "SQL" and checking_parent = Population Review Check then the count to get Total SQL Feedback will be distinct count of Item_ref in relation to the jira_id. e.g PPDQ-9387 will count as 2 but PPDQ-9225 will count as 1

[alish_b]

Hello @Kay_Kalu ,

Please provide some sample data in a format that can be copied (instead of a screenshot or image). That will help us to understand your requirement better and cook up a solution for your needs.

[Kay_Kalu]

jira_id	item_ref	comment_date	checking_parent	feedback category
PPDQ-9506	PR1	18/07/2025 16:52	Population Review Check	SQL
PPDQ-9387	PR1	01/07/2025 16:17	Population Review Check	SQL
PPDQ-9387	PR2	01/07/2025 16:17	Population Review Check	SQL
PPDQ-9350	PR1	16/06/2025 17:24	Population Review Check	SQL
PPDQ-9225	PR1	20/05/2025 17:21	Population Review Check	SQL
PPDQ-9225	PR1	01/07/2025 13:54	Population Review Check	SQL

From the table above I have filtered the feedback category = "SQL" and checking_parent = Population Review Check then the count to get Total SQL Feedback will be distinct count of Item_ref in relation to the jira_id. e.g PPDQ-9387 will count as 2 but PPDQ-9225 will count as 1

[alish_b · Accepted solution]

Hello @Kay_Kalu ,

Thank you for the sample data.

Please find the DAX below:

Total SQL Feedback = 
VAR FilteredTable =
    FILTER (
        FeedbackData,
        FeedbackData[feedback category] = "SQL"
    )
VAR DistinctPerJira =
    SUMMARIZE (
        FilteredTable,
        FeedbackData[jira_id],
        "DistinctItems", DISTINCTCOUNT ( FeedbackData[item_ref] )
    )
RETURN
SUMX ( DistinctPerJira, [DistinctItems] )

Now I loaded the sample data as a table called 'FeedbackData' so please update it with your table name and make adjustments as required.

I have structured the query in sequential blocks so it is easy to understand. First we filter the table for only feedback category = "SQL" (since we are interested in SQL Feedback). Next, we calculate the distinct count of item_ref  for each jira_id and finally sum that up. One assumption I took here is that you don't need this checking_parent = Population Review Check as a hard coded filter rather this will be provided at say row level in visual and needs to be dynamic (if it needs to be static, you can simply add it after the feedback category= 'SQL' condition as

&& FeedbackData[checking_parent] = "Population Review Check")

Hope it helps!