Power BI Dynamic Filter/Slicer

PrasadSwamy007 

Handle this in a measure:

Filtered Sales =
VAR SelectedSource = SELECTEDVALUE('SlicerTable'[Source])
RETURN
CALCULATE(
SUM('Table'[Sales]),
FILTER(
'Table',
'Table'[Source] = SelectedSource
|| (SelectedSource = "XYZ" && 'Table'[Sub-Source] = "pack")
)
)

Use a disconnected slicer table with distinct Source values. Do not connect it to your fact table via a relationship.

Create a disconnected table with all sources for use in the slicer. You can do this in DAX with

Source for Slicer = DISTINCT( 'Table'[Source] )

or you could do it in Power Query.

Do not connect this new table to your main table.

Create a measure like

Sales Measure = 
VAR CurrentSource = SELECTEDVALUE( 'Source for Slicer'[Source] )
VAR PackSales = CALCULATE(
    SUM( 'Table'[Sales] ),
    'Table'[Sub-Source] = "pack"
)
VAR SelectedSales = CALCULATE(
    SUM( 'Table'[Sales] ),
    TREATAS( { CurrentSource }, 'Table'[Source] )
)
VAR Result = IF( CurrentSource = "XYZ",
    SelectedSales + PackSales,
    SelectedSales
)
RETURN Result

Hello,

I’m not completely sure, but I think the easiest way is to keep the slicer on Source and handle the special XYZ logic inside a measure with SWITCH or IF.

Something like, if selected source = "XYZ" then calculate Sales where Source = "XYZ" OR Sub-Source = "pack", otherwise just filter normally by Source. That should give you 190 for XYZ, 60 for ABC and 240 for DEF.

Best regards,
Daniele

Hi PrasadSwamy007 ,

You can create a separate slicer calculated table to capture the user selection without directly filtering the main table.
The default slicer behavior only filters matching Source values and cannot dynamically include rows from another category when XYZ is selected.

Use this calculated table in the slicer and keep it disconnected from the main table.

SlicerSource =DISTINCT(test[Source]).

Step-2

This custom DAX measure is created to manually handle the filtering logic based on what the user selects in the slicer. Normally, the measure behaves like a regular filter and returns sales only for the selected source. However, the business requirement for XYZ is different — when users select XYZ, they also want to include all projects where Sub-Source = "pack" even if those rows belong to another source category like ABC.

To achieve this, the measure dynamically changes the filter context. It checks the selected slicer value and applies conditional logic.

For normal selections like ABC or DEF, it filters data normally
but for XYZ, it expands the filter condition to include both XYZ rows and pack rows.

you can create measure like this-

Custom Sales =

VAR SelectedSource =

   SELECTEDVALUE(SliderSource[Source])

RETURN

CALCULATE(

    SUM(test[Sales]),

    FILTER(

        test,

        (

            SelectedSource <> "XYZ"

            && test[Source] = SelectedSource

        )

        ||

        (

            SelectedSource = "XYZ"

            &&

            (

                test[Source] = "XYZ"

                || test[Sub-Source] = "pack"

            )

        )

    )

)

 

I hope this helps.

Let me know if you face any issues while implementing it.

Hi  PrasadSwamy007 

Could you please confirm if the issue has been resolved? If not, feel free to reach out if you have any further questions.

Your update would be helpful for other members who may face a similar issue.