Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mezga
Helper I
Helper I

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:

 

Test Image2.PNG

 

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. 

 

Test Image.PNG

 

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!

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Mezga
Helper I
Helper I

Anyone able to please help with this one?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.