Forum Discussion
Request for help creating a filter on a Matrix Table
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.
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
