Dynamic labels based on filter

[ben_w]

I have the requirement to show a dropdown box with "Yearly"; "Quarterly"; "Monthly"; "Season".

Based on this selection, graphs and tables should change their axis.

I work with SSAS Tabular 2016 in DirectQuery mode.

This is as it should be, monthly selected and monthly showing in the table axis.

Yet when you would select Yearly, it should change to showing "2015"; "2016"; "2017"; ...

The necessary columns to switch between are all available in the DIM_DATE dimension: "FiscalYear", "FiscalMonth"; "FiscalQuarter"; "Season"

My initial idea whas to create a calculated column, but then i realised these are evaluated at processing time. Testing it out proved this.

Any ideas, pointers and full blown solutions are welcome, thanks!

[v-huizhn-msft]

Hi @ben_w,

Calculated column are initialized regardless of Slicers. And for your requirement, the column name will be changed by slicer. Based on my under standing, the column name must come from one field of your resource table, right? The measure can changed as the slicer. Column can not change as the slicer.

Best Regards,
Angelia

[ben_w]

I don't find a way to accomplish to use a measure as a dimension (and thus causing other measures to calculate based on it); do you have more information about this?

[v-huizhn-msft]

Hi @ben_w,

Could you please create some sample table for test?

Best Regards,
Angelia

[ben_w]

I made a powerbi file with the data:

https://1drv.ms/u/s!AklJKrJGWTN6iEBeeIhnM819QE5U

It contains a tab with 4 different graphs named 'All different possibilities'. They all share the one measure, but have a different label out of the Dim_date.

These 4 graphs have to be one graph with a Filter box choosing which label to apply. I made another tab woth the filter box and preset the graph on it to Month. Changing the Filter box to 'Quarterly', should update the graph to show quarters instead of months.

Thank you for your time and interest,

[v-huizhn-msft]

Hi @ben_w,

I download your .pbix file, and I total understand your requirement. But we can't meet your needs using sclier. Because the X-axis must display column rather than measure, while the slicer can't control different columns, slicer is used to filter rows. Thanks for understanding.

Best Regards,
Angelia