Slicer not to filter the visual but to filter the data behind the visual

[daniel_baciu]

Hi,

I have a bar chart that i want it to get updated whenever the user updates the period in a slicer.  Here the measures are set manually to show actuals until January (P04), and Forecast from February onwards. 

Measure code below - you will notice i manually input "<= 4" (4 = January, as 1 = October) so that i could filter the data accordingly. I want that "4" to be linked to the "Period" in the slicer found in "Index" page. Currently i did not set up a sync between the slicer and this bar chart page.

Below the measure for Actuals:

Value k$ MTD Actuals Daily = 
DIVIDE(    
    CALCULATE(
        [Value All Data Demo k$],
        FILTER( 'Data All Demo',
            ('Data All Demo'[Scenario] = "ActualsFixed"   && 'Data All Demo'[Year] = "2023"     && 'Data All Demo'[Numeric Period] <= 4)   ||
            ('Data All Demo'[Scenario] = "PORFixed"       && 'Data All Demo'[Year] <> "2023")
        )
    ),
    SUM( 'Dim Number of Days (Bridge)'[# of Days])
)

Below the measure for Forecast:

Value k$ MTD Forecast Daily = 
DIVIDE(
    CALCULATE(
        [Value All Data Demo k$],
        FILTER( 'Data All Demo',
            ('Data All Demo'[Scenario] = "PORFixed"   && 'Data All Demo'[Year] = "2023"   && 'Data All Demo'[Numeric Period] > 4))
    ),
    SUM( 'Dim Number of Days (Bridge)'[# of Days] )
)

I could not sync the slicer with the chart, as it would get the following result... 

So... how to automatically filter the data on a bar chart using a slicer that should not filter the chart, but the data behind the chart? Basically, how to link the manual input "4" with the P04 (Period 4) selected in the "Index" tab?

Pbix here: https://drive.google.com/file/d/1QGMDcIWyz_IcNkEFBPszmWLAxJipWX7J/view?usp=share_link

[amitchandak]

@daniel_baciu , if the period is coming from a date table

you can create this as measure and use it

month(maxx(allselected(Date), Date[Date]) )

or

maxx(allselected(Date), Date[period])

[daniel_baciu]

@amitchandak  Many thanks for your reply!

I've tried all the below and none of them work correctly.....

Current Period = 
//MONTH(maxx(ALLSELECTED('Dim Calendar'), 'dim calendar'[date]))
//MONTH(maxx(ALLSELECTED('Dim Calendar'), 'Dim Calendar'[Period]))
//MONTH(maxx(ALLSELECTED('Dim Calendar'), 'Dim Calendar'[Fiscal Period]))
//MONTH(maxx(ALLSELECTED('Data Orders & Sales'), 'dim calendar'[date]))
//MONTH(maxx(ALLSELECTED('Data Orders & Sales'), 'Data Orders & Sales'[Period]))
//MONTH(maxx(ALLSELECTED('Data Orders & Sales'), 'Data Orders & Sales'[numeric Period]))
//MAXX(ALLSELECTED('Dim Calendar'), 'Dim Calendar'[date])
//MAXX(ALLSELECTED('Dim Calendar'), 'Dim Calendar'[period])
//MAXX(ALLSELECTED('Dim Calendar'), 'Dim Calendar'[Fiscal Period])
//MAXX(ALLSELECTED('Data Orders & Sales'), 'Data Orders & Sales'[Period])
//MAXX(ALLSELECTED('Data Orders & Sales'), 'Data Orders & Sales'[numeric Period])

 Do you have any other ideea?

Thanks!

[Anonymous]

HI @daniel_baciu,

You can create unconnected table as source of slicer, then you can extract the selection as condition to use in your expression:

Value k$ MTD Forecast Daily =
VAR selected =
    MAX ( NewTable[Date] )
RETURN
    DIVIDE (
        CALCULATE (
            [Value All Data Demo k$],
            FILTER (
                'Data All Demo',
                'Data All Demo'[Scenario] = "PORFixed"
                    && 'Data All Demo'[Year] = YEAR ( selected )
                    && 'Data All Demo'[Numeric Period] > MONTH ( selected )
            )
        ),
        SUM ( 'Dim Number of Days (Bridge)'[# of Days] )
    )

Regards,

Xiaoxin Sheng

[daniel_baciu]

Hi @Anonymous,

If i create an unconnected table for the slicer, then the slicer will not filter the other pages (my report has several pages, that are all filtered based on that slicer, except for this one with this chart). Is that correct? 

However, i tried your solution,  but it's not working as my calendar is a financial calendar different than a normal one, and i also get the error of not supporting comparing values of type text with integer...

Any other ideas please?

[Anonymous]

Hi @daniel_baciu,

In fact, current power bi does not support custom filter effect so normally we need to use unconnected table and measure filter to achieve standalone filter effects. You can add/remove the measure filter to control which visual response with filters.

Maybe you need to add a step to use the current date value to lookup calendar table find out correspond fiscal date. Then you can use this as filter conditions in formula.

Regards,

Xiaoxin Sheng