Forum Discussion

Suryateza's avatar
Suryateza
Frequent Visitor
1 year ago
Solved

Title: Default & Conditional Slicer Setup in Power BI

Hi all, I have a column called ugl, and I want to configure a slicer with specific default and conditional behavior. Here’s what I need: In the slicer for ugl, the following values should be exc...
Need Help
  • srlabhe's avatar
    srlabhe
    1 year ago
    To achieve this complex slicer behavior in Power BI, you will need to create a disconnected support table and a DAX measure. This approach separates the filtering logic for your visuals from the values presented in the slicer. 
    Step 1: Create a disconnected support table
    Create a new table that contains only the six values you want to show in the slicer dropdown. This table will be disconnected from your main data model, allowing you to control the slicer's available options independently.
    dax
    ugl Slicer Values = 
DATATABLE(
    "ugl Slicer", STRING, {{ "unplanned" }, { "UGL: 27ff" }, { "01.01.2028" }, { "01.09.2027" }, { "01.07.2027" }, { "01.04.2027" }}
)

     

    Step 2: Create a DAX measure for conditional filtering
    This measure will check if any values are selected in the new slicer. If nothing is selected (the default view), it will filter out the six specific values from your report visuals. If a user makes a selection, it will include the selected values and respect the slicer's filter context.
    dax
    Filter ugl = 
VAR SelectedUgl = VALUES('ugl Slicer'[ugl Slicer])
VAR ExcludedUgl = { "unplanned", "UGL: 27ff", "01.01.2028", "01.09.2027", "01.07.2027", "01.04.2027" }
RETURN
IF(
    // If no values are selected in the slicer...
    ISFILTERED('ugl Slicer'[ugl Slicer]) = FALSE(),
    // Filter out the excluded values from your main ugl column.
    IF(
        SELECTEDVALUE(YourDataTable[ugl]) IN ExcludedUgl,
        0, -- Exclude
        1  -- Include
    ),
    // If values are selected, respect the slicer's selection.
    IF(
        SELECTEDVALUE(YourDataTable[ugl]) IN SelectedUgl,
        1, -- Include selected values
        0  -- Exclude all others
    )
)

    Note: Replace YourDataTable with the actual name of your table that contains the ugl column. 

srlabhe's avatar
srlabhe
Icon for Super User rankSuper User
1 year ago
To achieve this complex slicer behavior in Power BI, you will need to create a disconnected support table and a DAX measure. This approach separates the filtering logic for your visuals from the values presented in the slicer. 
Step 1: Create a disconnected support table
Create a new table that contains only the six values you want to show in the slicer dropdown. This table will be disconnected from your main data model, allowing you to control the slicer's available options independently.
dax
ugl Slicer Values = 
DATATABLE(
    "ugl Slicer", STRING, {{ "unplanned" }, { "UGL: 27ff" }, { "01.01.2028" }, { "01.09.2027" }, { "01.07.2027" }, { "01.04.2027" }}
)

 

Step 2: Create a DAX measure for conditional filtering
This measure will check if any values are selected in the new slicer. If nothing is selected (the default view), it will filter out the six specific values from your report visuals. If a user makes a selection, it will include the selected values and respect the slicer's filter context.
dax
Filter ugl = 
VAR SelectedUgl = VALUES('ugl Slicer'[ugl Slicer])
VAR ExcludedUgl = { "unplanned", "UGL: 27ff", "01.01.2028", "01.09.2027", "01.07.2027", "01.04.2027" }
RETURN
IF(
    // If no values are selected in the slicer...
    ISFILTERED('ugl Slicer'[ugl Slicer]) = FALSE(),
    // Filter out the excluded values from your main ugl column.
    IF(
        SELECTEDVALUE(YourDataTable[ugl]) IN ExcludedUgl,
        0, -- Exclude
        1  -- Include
    ),
    // If values are selected, respect the slicer's selection.
    IF(
        SELECTEDVALUE(YourDataTable[ugl]) IN SelectedUgl,
        1, -- Include selected values
        0  -- Exclude all others
    )
)

Note: Replace YourDataTable with the actual name of your table that contains the ugl column.