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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PBI_KN8
New Member

How to Filter a Raw Data Table Based on Matrix Selection in Power BI?

Hello Power BI Community,

I have a Power BI report where I want to filter a raw data table based on a selection made in a matrix visual. Specifically, when I click on a value in the matrix (for example, "Met 30 Days" for a particular region and sub-region), I need the raw data table to display only the records matching the selected IS COMPLETED? status (like "Met 30 Days", "Did Not Meet 30 Days", etc.).

Current Setup:

I have two main tables:

  1. EAG (contains the raw data, including the fields: Region, Sub Region, Creation Date, Approval Date, Status, E_Completion, and IS COMPLETED?).
  2. Calendar (with a Date column and a [-30 Days] column for date manipulation).

Key Calculated Columns:

  • E_Completion: This calculated column categorizes records based on their completion status (e.g., "Completed Current Year", "Completed Prior Year", etc.).
  • IS COMPLETED?: This calculated column indicates whether the status is "Met 30 Days", "Did Not Meet 30 Days", etc.

Matrix Visual:

  • Rows: Region, Sub Region
  • Values: Measures like Met30DaysCount, DidNotMeet30DaysCount, RequiresCompletionNowCount, etc.

The matrix is set up to display counts of records for each IS COMPLETED? status (like "Met 30 Days"), for a specific Region and Sub Region.

The Issue:

When I select a value (e.g., "Met 30 Days") in the matrix, I want the raw data table to display only the corresponding records (e.g., only rows where IS COMPLETED? = "Met 30 Days"). However, currently, when I click on a matrix value, the raw data table still shows all records for the selected region and sub-region, not just the filtered ones.

What I've Tried:

  • I've created measures that count rows for each IS COMPLETED? value but applying them as filters doesn’t work as expected.
  • I've tried using a calculated column directly in the measure, but it doesn’t filter the raw data table as I hoped.

My Request:

  • Can anyone suggest a method (using DAX or Tabular Editor) that will dynamically filter the raw data table based on matrix selection, without showing all rows for the selected region and sub-region?
  • Would using Tabular Editor help achieve this, and if so, can you explain how to set it up step by step?

Thank you for your help!

**My Data**:-

RegionSub RegionCreation DateApproval DateStatusE_Completion
IS COMPLETED?
EastArea 12025-01-012025-01-15Aprooved
Completed Current 
Year
Met 30 Days
EastArea 12025-02-012025-02-10Aprooved
Completed Current
Year
 
Met 30 Days
EastArea 12024-03-012024-04-01Closed
Completed prior Year
 
Did Not Meet 30 Days
EastArea 22025-05-012025-06-01Aprooved
Completed Current
Year
 
Did Not Meet 30 Days
WestArea 32024-01-012024-04-01ClosedCompleted prior Year
Did Not Meet 30 Days
WestArea 42025-01-01 In Progress
Requires Completion Now
 
Requires Completion Now
CentralArea 52025-01-01 In Progress
Requires Completion Now
 
Requires Completion Now
CentralArea 62025-04-01 In Progress
Requires Completion in Future
 
Requires Completion in Future
       


 --Note: I'm looking only at current year values
OutPut:

**Matrix Visual**:

Region  Sub Region  Met 30 DaysDid Not Meet 30 DaysRequires Completion NowGrand Total %Total for 30days
EastArea 121 367%
 Area 2     
WESTArea 3     
 Area 4     
CentralArea 5  110%
 Area 6  11 0%

**Raw Data Table**:
- Fields: `Region`, `Sub Region`, `Creation Date`, `Approval Date`, `Status`, `E_Completion`, `IS COMPLETED?`.

**Desired Behavior**:
- When I click on  any value in  matrix table let say 2 for "Met 30 Days" in East, Area 1 in the matrix, the raw data table should show only the 2 records with `IS COMPLETED? = "Met 30 Days"`:
| Region | Sub Region | Creation Date | Approval Date | Status | E_Completion | IS COMPLETED? |
|--------|------------|---------------|---------------|------------|----------------------|-----------------|
| East | Area 1 | 2025-01-01 | 2025-01-15 | IApproved| Completed Current Year| Met 30 Days |
| East | Area 1 | 2025-02-01 | 2025-02-10 | Aprooved| Completed Current Year| Met 30 Days |

 

1 ACCEPTED SOLUTION
rohit1991
Super User
Super User

Hi @PBI_KN8

 

It is a limitation with how Power BI handles interactions with matrix visuals when you’re using calculated measures like % COMPLETED.

 

Basically, when you click on a value like “Met 30 Days” in the matrix, it doesn’t actually pass that filter down to your raw table because that value comes from a measure, not a field/column. So your raw data table doesn’t know what to filter by.

 

You can try these steps :

  1. Unpivot your data so “Met 30 Days”, “Met 60 Days”, etc. are actual values in a column  that way clicking on them works just like any other slicer or column filter.

  2. Or, if restructuring the data isn’t an option, you can use a disconnected table with values like “Met 30 Days”, use SELECTEDVALUE() to capture the selected item, and then use that in your filtering logic on the raw data.

Both methods are solid, just depends how flexible your model is.


Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

View solution in original post

5 REPLIES 5
v-ssriganesh
Community Support
Community Support

Hi @PBI_KN8,
Could you please confirm if your query have been resolved by the solution provided by? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

v-ssriganesh
Community Support
Community Support

Hi @PBI_KN8,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

v-ssriganesh
Community Support
Community Support

Hi @PBI_KN8,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

 

rohit1991
Super User
Super User

Hi @PBI_KN8

 

It is a limitation with how Power BI handles interactions with matrix visuals when you’re using calculated measures like % COMPLETED.

 

Basically, when you click on a value like “Met 30 Days” in the matrix, it doesn’t actually pass that filter down to your raw table because that value comes from a measure, not a field/column. So your raw data table doesn’t know what to filter by.

 

You can try these steps :

  1. Unpivot your data so “Met 30 Days”, “Met 60 Days”, etc. are actual values in a column  that way clicking on them works just like any other slicer or column filter.

  2. Or, if restructuring the data isn’t an option, you can use a disconnected table with values like “Met 30 Days”, use SELECTEDVALUE() to capture the selected item, and then use that in your filtering logic on the raw data.

Both methods are solid, just depends how flexible your model is.


Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
lbendlin
Super User
Super User

that sounds like standard functionality (assuming you mean "table visual" when you say "raw table"). What have you tried and where are you stuck?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.