Forum Discussion
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:
- EAG (contains the raw data, including the fields: Region, Sub Region, Creation Date, Approval Date, Status, E_Completion, and IS COMPLETED?).
- 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**:-
| Region | Sub Region | Creation Date | Approval Date | Status | E_Completion | IS COMPLETED? |
| East | Area 1 | 2025-01-01 | 2025-01-15 | Aprooved | Completed Current Year | Met 30 Days |
| East | Area 1 | 2025-02-01 | 2025-02-10 | Aprooved | Completed Current Year | Met 30 Days |
| East | Area 1 | 2024-03-01 | 2024-04-01 | Closed | Completed prior Year | Did Not Meet 30 Days |
| East | Area 2 | 2025-05-01 | 2025-06-01 | Aprooved | Completed Current Year | Did Not Meet 30 Days |
| West | Area 3 | 2024-01-01 | 2024-04-01 | Closed | Completed prior Year | Did Not Meet 30 Days |
| West | Area 4 | 2025-01-01 | In Progress | Requires Completion Now | Requires Completion Now | |
| Central | Area 5 | 2025-01-01 | In Progress | Requires Completion Now | Requires Completion Now | |
| Central | Area 6 | 2025-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 Days | Did Not Meet 30 Days | Requires Completion Now | Grand Total | %Total for 30days |
| East | Area 1 | 2 | 1 | 3 | 67% | |
| Area 2 | ||||||
| WEST | Area 3 | |||||
| Area 4 | ||||||
| Central | Area 5 | 1 | 1 | 0% | ||
| Area 6 | 1 | 1 | 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 |
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 :
-
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.
-
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.
-
5 Replies
- lbendlin
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?
rohit1991Hi 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 :
-
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.
-
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.
-
v-ssriganeshCommunity 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.
- v-ssriganesh
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
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.
