Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
bnisbet
Frequent Visitor

Report Filtering - Interactive Buttons

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)

 


Capture.PNG

 

 

10 REPLIES 10
MFelix
Super User
Super User

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@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]
        )
    ))

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelixSolved one problem and created another Smiley Tongue

 

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.....???

@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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Do you have any value selected in the slicer?

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix

 

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

 

 

Capture.PNG

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Vvelarde
Community Champion
Community Champion

 

@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] )
        )
    )

 




Lima - Peru

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.