Hello,
Hoping someone could please point me in the right direction with this matrix filtering issue that's driving me crazy.
I have three tables:
1. Project number (contains just a list of project numbers - I need this as a separate table, as it will filter multiple other tables not mentioned here).
2. Category - Contains three task category types, called "CS", "PC" and "CO. Again, I need this as a separate standalone table.
3. The main data table (Task List). It contains a heirarchy of fields for each task in the following order:
Project Number, System, Sub-System, Category, Task ID and Status.
I'm trying to build a matrix with the following columns:
1. System
2. Sub-System
3. Category
4. Completion status of tasks within each category (e.g. 1 of 2).
Thing is, I want the "Category" column to show all three categories (CS, PC and CO) regardless of whether there are any tasks in that field (instead showing something like "N/A").
I want to use the "Project Number" field from the Project Number table to filter the matrix. As such I've setup a One to Many relationship between those tables.
I'm using the below measure code for the completions status (i.e. point 4 above).
TASK COUNT =
VAR totalcount = COUNT('Task List'[Task ID])
var closedcount = CALCULATE(COUNT('Task List'[Task ID]),
FILTER('Task List',[Status]="Complete")
)
var return_value = IF(closedcount=BLANK(),0,closedcount)
return IF(totalcount=BLANK(),
"N/A",
return_value & " of " & totalcount
)
Problem is, when I filter between project numbers (from the related table), it still shows all the systems and sub-systems of the unselected project. I want those filtered out.
E.g. for Project 45954, I just want systems 01 to 05 to show.
As mentioned, I still want all three categories (CS, PC and CO) to show regardless of whether there are any tasks in those categories - and instead show "N/A".
Below is a link to a test file for explanation.
https://drive.google.com/drive/folders/1JdJz970qrNWDyo0PJnOFUwBcCJFWRcjo?usp=sharing
Any help would be greatly appreciated!
