Exclude slicer from filtering and filterby measure value in another column

[Anonymous]

Hello Everyone,

This is my first post, so I would like to say 'Hi' to all of you 🙂 

I am playing with PowerBI for a few weeks now and I come accross the issue I cannot solve for a few days. I have serached and tried few solutions, but none of them worked. Maybe someone had similar issue and will be able to help?

Basically- I would like to calculate a measure (%) which will take into consideration filters from every slicer except the Year-Month slicer and instead, add the filter from other column based on other measure value.

To give you more details:

Situation looks like this- I have a table 'KPI' that has several slicers- country, partner, Year-Month and others.
I've aded custom column to my table that converts Month-Year value to Fiscal Quarter and Year (both formated as strings, these are not dates), for example value 'KPI'[Year-Month] = '2018 November' is equivalent to 'KPI'[FQ & FY] = Q1 2019.

I have calculated the measure (SLICERtoQuarter) that converts selected value from a Year-Month slicer to the FQ & FY value (shown below):

SLICERtoQuarter = 
VAR Slicer_Selection =
SELECTEDVALUE ( 'KPI'[Year-Month], MAX('KPI'[Year-Month]))
Var Year_slicer = LEFT(Slicer_Selection,4)
Var Year_1 = Year_slicer + 1
Var Month_slicer = FORMAT(RIGHT(SUBSTITUTE(Slicer_Selection," ","-"),LEN(SUBSTITUTE(Slicer_Selection," ","-"))-SEARCH("-",SUBSTITUTE(Slicer_Selection," ","-"))), "string")
VAR Quarter =  
    SWITCH( TRUE(),
        Month_slicer = "November", CONCATENATE("Q1 " , Year_1),
        Month_slicer = "December", CONCATENATE("Q1 " , Year_1), 
        Month_slicer = "January", CONCATENATE("Q1 " , Year_slicer), 
        Month_slicer = "February", CONCATENATE("Q2 " , Year_slicer), 
        Month_slicer = "March", CONCATENATE("Q2 " , Year_slicer), 
        Month_slicer = "April", CONCATENATE("Q2 " , Year_slicer), 
        Month_slicer = "May", CONCATENATE("Q3 " , Year_slicer), 
        Month_slicer = "June", CONCATENATE("Q3 " , Year_slicer), 
        Month_slicer = "July", CONCATENATE("Q3 " , Year_slicer), 
        Month_slicer = "August", CONCATENATE("Q4 " , Year_slicer), 
        Month_slicer = "September", CONCATENATE("Q4 " , Year_slicer),
        Month_slicer = "October", CONCATENATE("Q4 " , Year_slicer),
    BLANK()
    )
Return
Quarter

The output seems correct as it displays expected value on card visual. Whenever I choose 2018 November from the slicer, the measure returns 'Q1 2019'.

Now, I would like to create a measure that will:
- consider selections from all slicers except Year-Month slicer
- additionally, add filter to column 'KPI''[FQ & FY] = SLICERtoQuarter value

In our example, it should additionally filter table by Q1 2019 and not '2018 November'.
My current emasure looks like this:

test = 
 VAR kwartal = [SLICERtoQuarter]
 RETURN
    CALCULATE(
        (<expression>,
        'KPI'[FQ & FY] = kwartal,
        ALL('KPI'[Year-Month] )
        )

I tried also withh ALLEXCEPT filter, but with no luck.

Unfortunately, measure calculation is incorrect.
Does anyone know what am I missing here?

Any help would be much appreciated.
Thank You,
Pawel

[v-chuncz-msft · Accepted solution]

@Anonymous ,

You may try Visual interactions and select the no impact icon.

[Anonymous]

@v-chuncz-msft ,

Thank You for the suggestion. I had it switched off, and apparently, it was the reason It did not work. My assuption is that, if I did not have 'ALL('KPI'[Year-Month] )' then it would work, but since my interaction was switched off it messed somehow with the engine.

Regards,
Pawel