Forum Discussion
Request for help creating a filter on a Matrix Table
I am fairly new to Power BI and Dax, and I am trying to add a filter on a Matrix Table object on a page.
here is my example dataset:
| Location | Time | Person |
| a | 1 | joe |
| a | 2 | sam |
| a | 3 | ed |
| a | 4 | fred |
| b | 1 | fred |
| b | 2 | ed |
| b | 3 | sam |
| b | 4 | joe |
| c | 1 | sam |
| c | 2 | joe |
| c | 3 | fred |
| c | 4 | ed |
I would like to have a user selectable control, maybe a slicer, to pick a single Person, like from a parameter, then have an expression filter the dataset, keeping only the records with a Time greater than or equal to the Time of the selected Person for each Location.
so if the user picks "ed", the resulting dataset would be:
| Location | Time | Person |
| a | 3 | ed |
| a | 4 | fred |
| b | 2 | ed |
| b | 3 | sam |
| b | 4 | joe |
| c | 4 | ed |
How can I create this experience in Power BI?
something I have tried, creating a calculated table of distinct Person values, adding a slicer based on that, then trying to use SELECTEDVALUE(), but because that function is evaluated later, it does not seem to be able to filter the table object, nor be used in a CREATETABLE() function or even FILTER() in a CALCULATE() and MAXX() to filter the records in a Matrix Table object. I might be close with those functions, but just not using them in the correct sequense.
It is like I am trying to filter with a Measure, but I am not able to get a Measure to filter Rows.
Thank you for any help you can offer!
Hey joemako ,
To achieve that, we will use a disconnected table for the slicer to avoid direct filtering of rows.
Steps:
1. Create a Disconnected Table for the Slicer:
Create a new table to use as a slicer, which contains the distinct persons but is not linked to the main data table.SlicerPersonTable = DISTINCT(SELECTCOLUMNS('Table', "Person", 'Table'[Person]))
2. Add the Slicer for Person:
Use the SlicerPersonTable[Person] column in a slicer to allow users to select the person.
3. Create the Filtering Measure:
Now, create a measure that will allow filtering based on the selected person’s time but keep all other rows for the same location:
FilteredRows =
VAR SelectedPerson = SELECTEDVALUE('SlicerPersonTable'[Person]) -- Get the selected person from the slicer
VAR SelectedPersonTime =
CALCULATE(
MAX('Table'[Time]), -- Get the time of the selected person at the location
ALLEXCEPT('Table', 'Table'[Location]),
'Table'[Person] = SelectedPerson
)
RETURN
IF(
MAX('Table'[Time]) >= SelectedPersonTime, -- Show rows with time >= selected person's time
1,
0
)
4. Apply the Measure as a Filter:Go to your Matrix visual.
In the Filters on this visual pane, drag the FilteredRows measure and set it to show only values where FilteredRows = 1.
4 Replies
Hey joemako ,
To achieve that, we will use a disconnected table for the slicer to avoid direct filtering of rows.
Steps:
1. Create a Disconnected Table for the Slicer:
Create a new table to use as a slicer, which contains the distinct persons but is not linked to the main data table.SlicerPersonTable = DISTINCT(SELECTCOLUMNS('Table', "Person", 'Table'[Person]))
2. Add the Slicer for Person:
Use the SlicerPersonTable[Person] column in a slicer to allow users to select the person.
3. Create the Filtering Measure:
Now, create a measure that will allow filtering based on the selected person’s time but keep all other rows for the same location:
FilteredRows =
VAR SelectedPerson = SELECTEDVALUE('SlicerPersonTable'[Person]) -- Get the selected person from the slicer
VAR SelectedPersonTime =
CALCULATE(
MAX('Table'[Time]), -- Get the time of the selected person at the location
ALLEXCEPT('Table', 'Table'[Location]),
'Table'[Person] = SelectedPerson
)
RETURN
IF(
MAX('Table'[Time]) >= SelectedPersonTime, -- Show rows with time >= selected person's time
1,
0
)
4. Apply the Measure as a Filter:Go to your Matrix visual.
In the Filters on this visual pane, drag the FilteredRows measure and set it to show only values where FilteredRows = 1.
Thank you for this Marcel, this gets me the solution I am looking for!
In order to help me better understand what is happening here:What is happening when I remove the field Person or Location from the Matrix visual? currently it gives different results. Is is possible to write this expression so it works even if Person or Location is not a field used in the Matrix Visual?
Additionally, what if not every person was in every location? for example if the last record of "c,4,ed" was changed to "c,4,jim" then when "ed" is selected, there is no filter happening on location "c", all records for that location are returned.
Your solution works great for this specific situation, and I'm afraid my current understaning of Power BI and Dax prevent me from understanding how to extend this expresion to work in more complex situations.
Hey joemako
What Happens When You Remove "Person" or "Location" from the Matrix?
The
ALLEXCEPT('Table', 'Table'[Location])in your measure is specifically telling Power BI to keep only the filter onLocationwhen calculating theSelectedPersonTime. This ensures that the measure looks for the selected person's time only at the current location.When you remove "Person" or "Location" from the matrix, it changes the filtering context:
-
Removing "Person": The measure might still work correctly because the person is being selected by the slicer, but it could affect how rows are displayed in the visual, as
MAX('Table'[Person])won’t work directly when thePersonfield is not used. -
Removing "Location": This will break the logic, as the measure depends on filtering by location (
ALLEXCEPT), and withoutLocation, it can no longer identify the correct location-specific time for the selected person.
To make the measure work even if "Person" or "Location" is not used in the matrix, we need to adjust the measure slightly to make it less dependent on visual context.
Handling Cases Where Not Every Person is in Every Location (e.g., "ed" is not in Location "c")
In the current version of the measure, when "Ed" is selected but there is no "Ed" in location "c", the filter for location "c" doesn't work correctly, and it returns all rows for that location.
To fix this, we need to ensure that the measure returns no rows for locations where the selected person is absent. We can adjust the measure to include a check to see if the selected person exists in the current location. So the adjusted meauser it will be like this:
FilteredRows_improved =
VAR SelectedPerson = SELECTEDVALUE('SlicerPersonTable'[Person]) -- Get selected person from slicerVAR SelectedPersonTime =CALCULATE(MAX('Table-Person'[Time]), -- Get the time of the selected person in the location'Table-Person'[Person] = SelectedPerson,ALLEXCEPT('Table-Person', 'Table-Person'[Location]))VAR PersonExistsInLocation =CALCULATE(COUNTROWS('Table-Person'),'Table-Person'[Person] = SelectedPerson,ALLEXCEPT('Table-Person', 'Table-Person'[Location]))RETURNIF(PersonExistsInLocation > 0 && -- Ensure the selected person exists in the locationMAX('Table-Person'[Time]) >= SelectedPersonTime, -- Compare times1,0)The result is bellow.
Let me know if this works or if you need further clarification!-
what do you think of this modification (to the conditional statement in your RETURN) to help this work when a person is not at a location, so when there is no time value for that location and perosn combination, when "SelectedPersonTime" is blank, and to fiter those records out:
AND( NOT ISBLANK(SelectedPersonTime) , MAX('Data'[Time]) >= SelectedPersonTime ), -- Show rows with time >= selected person's time
