Forum Discussion

Mezga's avatar
Mezga
Icon for Helper I rankHelper I
3 years ago
Solved

Filter Matrix from Related Table - Unexpected Results

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!

 

4 Replies

    • Mezga's avatar
      Mezga
      Icon for Helper I rankHelper I

      That's done the trick Ashish - thanks very much for your time mate!