Slicer Relationship

[Kay_Kalu]

I have a table with two columns: AdjustedDueDate_duration_format and DueDate_duration_format. I’ve created separate lookup tables to assign readable names to the numeric values in each column.

   

What I’m trying to do is create a slicer using the name column, so that when a value is selected, it filters both date columns accordingly. However, I suspect this might not be possible due to conflicting values between the two columns.

Still, I wanted to put this out there in case someone has found a workaround or a creative solution to make this work.

[DataNinja777 · Accepted solution]

Hi @Kay_Kalu ,

You are correct in your suspicion; a standard slicer relationship in Power BI cannot filter two separate columns in your data table based on a single selection. This is because a table can only have one active relationship path to another table at a time. Trying to filter both DueDate_duration_format and AdjustedDueDate_duration_format with one slicer creates an ambiguous path that Power BI cannot resolve through relationships alone.

However, there is a powerful and common pattern to solve this using a disconnected table for your slicer and a custom DAX measure to define the filtering behavior. First, you should create a single, unified table that will serve as the source for your slicer. You can do this using the "Enter Data" feature in the Home tab. This table should contain the unique ID and Name values for the statuses, and you can name it something like StatusSlicer_Table. It is critical that this new table has no relationships to your main data table in the model. You can then hide your original two lookup tables from the report view to avoid confusion.

Once the disconnected slicer table is in your model, the next step is to create a DAX measure that contains the filtering logic. You can add this measure to your main data table. This measure will check which value is selected in the new slicer and then use that value to check against both of your duration format columns.

Slicer Filter Logic = 
VAR SelectedStatus = SELECTEDVALUE(StatusSlicer_Table[StatusID])
RETURN
IF(
    ISBLANK(SelectedStatus),
    1,
    IF(
        SELECTEDVALUE(Main_Data_Table[DueDate_duration_format]) = SelectedStatus ||
        SELECTEDVALUE(Main_Data_Table[AdjustedDueDate_duration_format]) = SelectedStatus,
        1,
        BLANK()
    )
)

This DAX code works by first capturing the selected StatusID from your new slicer table. If no value is selected, it returns 1 for all rows, effectively showing all data. If a value is selected, it evaluates each row in your visual to see if the value in DueDate_duration_format OR the value in AdjustedDueDate_duration_format matches the selected status ID. If either condition is true, the measure returns a 1; otherwise, it returns BLANK().

To apply this logic, create a slicer visual on your report canvas and use the StatusName column from your new StatusSlicer_Table. Then, for any visual you want to control (like a chart or table), you must drag the [Slicer Filter Logic] measure into the "Filters on this visual" pane. Set the filter's condition to "is not blank" and apply it. The visual will now only display rows where the measure returned a 1, effectively filtering your data based on your slicer selection across both columns. You will need to apply this measure as a filter to every visual you wish the slicer to affect.

Best regards,

[v-pnaroju-msft · Accepted solution]

Thankyou, @mark_endicott, @DataNinja777, for your responses.

Hi Kay_Kalu,

We sincerely appreciate your inquiry posted on the Microsoft Fabric Community Forum.

Kindly find attached the screenshot and PBIX file, which may assist in resolving the issue. The solution includes a slicer table (StatusSlicer), a bridge table (NormalizedStatus) that consolidates both columns, relationships enabling slicer-based filtering, and a ShowRow DAX measure for filtering of visuals.

If you find our response helpful, we would be grateful if you could mark it as the accepted solution and kindly provide kudos. This will benefit other community members facing similar queries.

Should you have any further questions, please feel free to reach out to the Microsoft Fabric community.

Thank you.

[DataNinja777 · Accepted solution]

Hi @Kay_Kalu ,

You are correct in your suspicion; a standard slicer relationship in Power BI cannot filter two separate columns in your data table based on a single selection. This is because a table can only have one active relationship path to another table at a time. Trying to filter both DueDate_duration_format and AdjustedDueDate_duration_format with one slicer creates an ambiguous path that Power BI cannot resolve through relationships alone.

However, there is a powerful and common pattern to solve this using a disconnected table for your slicer and a custom DAX measure to define the filtering behavior. First, you should create a single, unified table that will serve as the source for your slicer. You can do this using the "Enter Data" feature in the Home tab. This table should contain the unique ID and Name values for the statuses, and you can name it something like StatusSlicer_Table. It is critical that this new table has no relationships to your main data table in the model. You can then hide your original two lookup tables from the report view to avoid confusion.

Once the disconnected slicer table is in your model, the next step is to create a DAX measure that contains the filtering logic. You can add this measure to your main data table. This measure will check which value is selected in the new slicer and then use that value to check against both of your duration format columns.

Slicer Filter Logic = 
VAR SelectedStatus = SELECTEDVALUE(StatusSlicer_Table[StatusID])
RETURN
IF(
    ISBLANK(SelectedStatus),
    1,
    IF(
        SELECTEDVALUE(Main_Data_Table[DueDate_duration_format]) = SelectedStatus ||
        SELECTEDVALUE(Main_Data_Table[AdjustedDueDate_duration_format]) = SelectedStatus,
        1,
        BLANK()
    )
)

This DAX code works by first capturing the selected StatusID from your new slicer table. If no value is selected, it returns 1 for all rows, effectively showing all data. If a value is selected, it evaluates each row in your visual to see if the value in DueDate_duration_format OR the value in AdjustedDueDate_duration_format matches the selected status ID. If either condition is true, the measure returns a 1; otherwise, it returns BLANK().

To apply this logic, create a slicer visual on your report canvas and use the StatusName column from your new StatusSlicer_Table. Then, for any visual you want to control (like a chart or table), you must drag the [Slicer Filter Logic] measure into the "Filters on this visual" pane. Set the filter's condition to "is not blank" and apply it. The visual will now only display rows where the measure returned a 1, effectively filtering your data based on your slicer selection across both columns. You will need to apply this measure as a filter to every visual you wish the slicer to affect.

Best regards,

[mark_endicott]

@Kay_Kalu if you have two separate _format columns, and two separate Name tables that produce the _Name columns, and both these separate tables have the corresponding _format codes, you will be able to create two separate relationships, that allow you to use two separate slicers with the _name columns that will filter your table.

No workarounds necessary. 

If this does not answer your question, please provide some more detail. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[Kay_Kalu]

Thanks for you response but what really was hoping do is have a single slicer for the both.