Forum Discussion

Suryateza's avatar
Suryateza
Frequent Visitor
11 months 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
    11 months 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. 

grazitti_sapna's avatar
grazitti_sapna
Icon for Super User rankSuper User
11 months ago

Hi Suryateza,

 

Create a disconnected table with the values you want to excelude

 

UGL_Slicer =
DATATABLE(
"UGL", STRING,
{
{"01.04.2027"},
{"01.07.2027"},
{"01.09.2027"},
{"01.01.2028"},
{"UGL: 27ff"},
{"unplanned"}
}
)

Now Create a measure like below

UGL_Filtered =
VAR SelectedValues = VALUES(UGL_Slicer[UGL])
VAR IsAnySelected = COUNTROWS(SelectedValues) > 0
RETURN
IF (
-- If any value in slicer is selected, include default data + selected values
IsAnySelected,
NOT( 'YourTable'[ugl] IN EXCEPT( SelectedValues, BLANK() ) ),
-- If nothing selected, exclude default 6 values
NOT( 'YourTable'[ugl] IN { "01.04.2027", "01.07.2027", "01.09.2027", "01.01.2028", "UGL: 27ff", "unplanned" } )
)

 

Use this measure as your visual filter

 

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!