Report Filtering - Interactive Buttons

[bnisbet]

Hi Folks,

I am looking to create an interactive filter of sorts.

The idea is the user would click the financial year data slicer at the top of the page, this is currently just a column with each financial year located in its own cell. The aim is that this would then change the rest of the Data (Achieved, Budget and Achieved %) to the correct financial year. The issue is that each data set does not have a common name, also the two Achieved Indicators are measured columns.

Is this possible or is there an easier alternative?

An example of the current Achieved code is below:

Value Achieved = 
VAR ProbableVal =
    CALCULATE (SUM( Opportunity[Revenue 2017/18] ),
        Opportunity[Pipeline Reporting Status] = "F) Probable",
        Opportunity[Status Code] = "Live"
    )
VAR SecuredleVal = (
     
        SUM (Databanks[2017/18]
		))
RETURN  (ProbableVal + SecuredleVal)

[MFelix]

Hi @bnisbet,

I think that what you need is something like what i suggest in the post below please check it and tell something if you have any doubts,

community.powerbi.com/t5/Desktop/Switching-Values-on-a-report-with-a-splicer/m-p/93741#M39562

Regards

Mfelix

[bnisbet]

@MFelixThanks for pointing me in the right direction!

Having an issue with the formula, wondering if you could help?
I've attached the part that's causing an issue below, I  that the SUM function is wrong potentially believe?

VAR SecuredleVal =
    CALCULATE(SUM (
        SWITCH (
            FYSelector[Column1] = "2017/18",
            Databanks[2017/18], FYSelector[Column1] = "2018/19",
            Databanks[2018/19], FYSelector[Column1] = "2019/20",
            Databanks[2019/20]
        )
    ))

[MFelix]

Hi,

I believe that the problem is related with the order of the sum and the switch, try to do the switch function first and a sum for each result so in your case you would have 3 sum within the switch.

Mfelix

[bnisbet]

@MFelixSolved one problem and created another

Getting an error, saying it cannot determine a signal value for the FYSelector, unsure as each line has = "2017/18" and so on which, matches the value in each row.....???

[MFelix]

@bnisbet,

Are you placing the sum in the correct way the formula should look like this

VAR SecuredleVal =
CALCULATE(
SWITCH (
FYSelector[Column1] = "2017/18",
Sum(Databanks[2017/18]), FYSelector[Column1] = "2018/19",
Sum(Databanks[2018/19]), FYSelector[Column1] = "2019/20",
Sum(Databanks[2019/20])
)
)

It appears to me you are including the selector in the sum formula.

Regards,

Mfelix

[bnisbet]

Yep, mines matches?

VAR SecuredleVal =
    CALCULATE (
        SWITCH (
            FYSelector[Column1] = "2017/18", SUM ( Databanks[2017/18] ), 
            FYSelector[Column1] = "2018/19", SUM ( Databanks[2018/19] ), 
            FYSelector[Column1] = "2019/20", SUM ( Databanks[2019/20] )
        )
    )

@MFelix

[MFelix]

Do you have any value selected in the slicer?

[bnisbet]

@MFelix

Yeah, the error is occuring before I have a chance to complete the measure though.

[MFelix]

What us happening is that you are refering to a column and not yo the selectd value in the slicer, in FYSelector you need to have a measure thst gets the max or min of column 1 and then refered thst measure in your switch formula. If you refer the max if no selection is made you get 2020 min you will get 2017

[Vvelarde]

@bnisbet

Just made a little change to the DAX

VAR SecuredleVal =
    CALCULATE (
        SWITCH (
            Values(FYSelector[Column1]) = "2017/18", SUM ( Databanks[2017/18] ), 
            Values(FYSelector[Column1]) = "2018/19", SUM ( Databanks[2018/19] ), 
            Values(FYSelector[Column1]) = "2019/20", SUM ( Databanks[2019/20] )
        )
    )