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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Nicci
Helper I
Helper I

Ability to create a measure based on selection for a visual OR use anotheslicer if none is selected?

Hi Everyone,

I'm going to try my best to articulate what I'm looking for, but I have a bar chart where my x axis is fiscal years (last 10 years) and my Y axis is how much money was spent for each. 

 

I also have a gauge that shows total spent and paid visual as well. My issue I'm having is I want my gauge to default to this fiscal year we're in, but update if another fiscal year from the bar chart is selected. My problem i'm having is I dont know if there is a way to set it to default if nothing is selected. Right now with all the bar chart's FYs showing the gauge is totaling all of them up together which is inaccurate since our FYs act in silo's so I dont want mass total. So I want the guage to be this FY or adjust if a single year from the chart is selected and if nothing in the chart is selected then gauge goes back to the current FY.


I'm hoping i'm making sense. Is this even possible? What I tried to do is add a FY slicer with today's FY as the first/default so that keeps the guage for this FY and turn off the bar chart to filter from that slicer, but then if i select a year from the bar chart to view the guage is blank. 

 

I am hoping there's a way to create a measure and reference the selected item from the chart otherwise use the FY slicer i added or something like that.

 

Thank you!

5 REPLIES 5
Coriel-11
Resolver I
Resolver I

Hi @Nicci 
It's hard for to be certain without seeing some raw data, but have a look at the HASONEVALUE function. You should be able to use that inside an IF function so that they've clicked on a bar & thus filtered it down to one value it will filter on that value, but otherwise will ignore all filters. Something like:

 

Gauge measure = 
IF(
  HASONEVALUE([Bar Chart Measure]),
  [Bar Chart Measure], 
  CALCULATE([Bar Chart Measure],ALL(Dates)
)

 

Where "[Bar Chart Measure]" is whatever measure is driving you bar chart

Hope that's helpful in some way,
Matt

 

Hoping this helps for a visual:

 

Bad: What i dont want, so if nothing selected I want to default this in the back end somehow to the active fiscal year:

Nicci_0-1683309122356.png

 

Good when i click on a FY from the bar chart, but I want to be able to set a default for when you first come to this page and nothing is selcted to show for today's FY, but i want it to change if i pick a past fiscal year:

Nicci_1-1683309229713.png

 

 

I tried adding a FY slicer drop down, but if I do that my bar chart changes to show the selected only, i need the bar chart to stay showing all the FYs. I did exclude the barchart from the slicer, but that causes another issue where the gauge will use the FY slicer added but if i click a bar chart year the gauge doesnt adjust and goes blank.

 

Could you provide some dummy data in the format it's in?

Sure here is some dummy data for the setup I have:

 

Payment table:

FYtotalspaid
2002200315000010000
200320042000015000

 

On the bar chart its simply using the FY column for x axis and totals column for y axis (sum of totals).

 

On the gauge its the same table, Its using sum of paid column for the value and sum of totals for the max value.

Thanks, though it didnt work. Having issues where i cant use the column measure (fiscal year) for the true expression, not recognizing. Theres no way to reference a visual? The issue is the bar chart uses fiscal year on the x axis, but the gauge is just money related, i dont have a specific fiscal year filter on it, it just auto adjusts if i select a fiscal year from the bar chart. I want that but if nothing selected i dont want it to recalculate to be a fiscal year filter of all (because nothing is selected, or maybe all is selected since bar chart shows all FY). 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.