Filter Matrix from Related Table - Unexpected Results

[Mezga]

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!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Mezga]

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

[Ashish_Mathur]

You are welcome.  Please mark my previous reply as Answer.

[Mezga]

Anyone able to please help with this one?