Dynamically select measures to be shown on a Power BI visual via slicer

A few days ago a client asked me if it is possible to dynamically change series displayed on Power BI chart. My first (instinctive) answer was “Yes, of course, you can use a slicer to select which series you want to show, just put desired column in a slicer visual”. But then he added details: he wanted to select a measure to display on a chart, not to filter a value from column. My second (instinctive) answer was “No, you can’t. You can only filter a column, and can’t place measures in a slicer”.

But after a little chat I started to wonder whether it is really impossible. If we put a measure in a “Value” well of chart fields, it will be shown as a series (for example, some [Total Amount] measure). What my client is actually wants? He want to choose some elements on the slicer and, if one element selected, to show a measure. If that element is unchecked, then don’t show a measure.

Actually, those slicer’s elements are unique values from some column. A slicer applies a filter to that column. Can we catch whether a column is filtered? Yes, of course, we can do it with DAX. And if some desired value is selected, we just need to show a measure as a series. As that measure is already in the “Value”  well of a chart, then, in other words, we just have to “do nothing”. So, we only need to somehow hide a measure if a desired slicer’s element didn’t selected.

I only know one way to “hide” a measure value. Usually Power BI (or Power Pivot and simple Pivot Table) shows nothing for the BLANK values of measure.

So, here comes a solution:

If some value “A” is checked (selected) on a slicer, then measure calculates and displays as usual. If value “A” is unchecked (not selected) on a slicer, then measure should return a BLANK().

After this logic was unraveled, the implementation became quite easy.

First, we need a special disconnected SlicerTable with at least one [SlicerColumn] column, and a [RawMeasure] which actually make all calculations needed. Then we make a new (I shamelessly call it “shy”) measure, which will behave as described above.

Here is a one of possible patterns for “shy” measures:

Shy Measure for Slicing on a Chart =
IF (
SlicerTable[SlicerColumn] = “Beautified Measure Name”
[Raw Measure],
    BLANK ()

There is a little bit shorter measure, I think, it is even better:

Shy Measure for Slicing on a Chart =
IF (
SlicerTable[SlicerColumn], “Beautified  Measure Name”
[Raw Measure],
    BLANK ()

To test these measures I’ve opened Power BI Desktop and created a simple table with unique name ‘Table1’ (dates there in DD.MM.YYYY format) via “Enter Data” button:

Name Dates Score
John 01.01.2017 5
John 01.02.2017 7
John 01.03.2017 6
John 01.04.2017 8
John 01.05.2017 8
John 01.06.2017 5
Jack 01.01.2017 7
Jack 01.02.2017 9
Jack 01.03.2017 8
Jack 01.04.2017 7
Jack 01.05.2017 6
Jack 01.06.2017 5
Jenny 01.01.2017 9
Jenny 01.02.2017 10
Jenny 01.03.2017 6
Jenny 01.04.2017 7
Jenny 01.05.2017 8
Jenny 01.06.2017 9


And then created another ‘SlicerTable’ table:

SlicerColumn SlicerOrder
Average Score 2
Total Score 1


After loading these tables to a model, I’ve created two simple “raw measures”:

AvgScore =
AVERAGE ( Table1[Score] )

TotScore =
SUM ( Table1[Score] )

Then we need to create a “shy” version of the each measure:

Average Score =
IF (
    CONTAINS ( ‘SlicerTable’, SlicerTable[SlicerColumn], “Average Score” ),
    BLANK ()

Total Score =
IF (
    CONTAINS ( ‘SlicerTable’, SlicerTable[SlicerColumn], “Total Score” ),
    BLANK ()

Ok, lets add some visuals and a slicer.

I choose a simple bar chart with Names on X-axis, and a “Line chart” with dates on X-axis, and put my “shy” measures in the values:

Two simple charts to test

Two simple charts to test

All we need now is too create a slicer with the [SlicerColumn] from the ‘Slicer’ table and test it:

Test for Line chart

Test for Line chart

We’ve got it! Our new measure hides if unchecked on the slicer, and appears if checked.

The same behavior we can see on other charts:

Test for Bar Chart

Test for Bar Chart

You see, it is easy.

The only issue I’ve found Is that on “Line chart”, when we select only one measure, which is the first in the ‘Value’ well of visual, the Legend mystically disappeared, but if we select any other, all or none of slicer values, Legend comes back.

I also highly recommend this nice community blog post by Sam McKay “Dynamically change the information within a visual via a slicer”, where he described a way to select only one measure to be shown on a chart. I’ve discovered his post after I finished my solution for the same task. Don’t be like me and don’t reinvent a bicycle. Google before, not after.

DAX code in this post beautified with the lovely and charming www.daxformatter.com

A good reading for you:

Share this
On lazy value evaluation order in Power Query and Power BI
Excel sheet as a source to Power Query and Power BI: a pitfall of UsedRange


  1. whoah this weblog is excellent i like reading your posts.
    Stay up the good work! You already know, lots of people are looking round for this information, you could aid them greatly.

  2. Hi Maxim,

    That was a great post. you describe it well and presenting an example which was really helpful.
    You solved my problem.


  3. hi.i’ve used SELECTEDVALUE instead.it is simpler than CONTAIN

    • Maxim Zelensky
      April 25, 2018 - 09:10

      Hi Hamid. Yes, SELECTEDVALUE works good, but only if you have one value selected on the slicer. If your slicer allows multiselection, the SELECTEDVALUE will return it’s second argument or blank value, but not a list of selected values.

  4. Hi! I just wanted to say thank you for publishing this article. It helped me A LOT and I believe I wouldn’t have figured out how to eventually make it. 🙂

  5. I am sorry for 2 comment
    And more the legend being dynamical too

  6. Maxim, Hello!
    (Finally I found Your site ;))
    If I right understand Stephanie ~> We can create 1 measure (in the end)~> add measure in Values~>SlicerColumn add to Legend and add to Axis – Names/Dates.
    Create Slicer with SlicerColumn

    TEST =
    IF (
    HASONEVALUE ( ‘SlicerTable'[SlicerColumn] );
    SWITCH (
    VALUES ( ‘SlicerTable'[SlicerColumn] );
    “Average Score”; [AvgScore];
    “Total Score”; [TotScore];
    BLANK ()

    • Hi Illya,
      thanks for the comment – looks very interesting. Didn’t checked it on file, but I think that I catch your idea.
      If we put SlicerColumn on Legend, then we can use one measure which will then show us values, filtered by a slicer on the same SlicerColumn.
      It’s a very nice trick, and it is very useful when your measures are of the same format – really, you don’t need to create duplicate ‘shy’ measures, excellent!

      Unfortunately, there some limits… If you want to show measures of different formats, or use more complex visuals (for example, bar chart with lines), this won’t work – one measure can has only one format, and you cannot place more measures to values if you use Legend on the line visual, etc. But for simple scenarios your approach is very good, thanks!!!


      PS I think, with the new SELECTEDVALUE function we can shorten the code:
      TEST =
      SWITCH (
      SELECTEDVALUE ( ‘SlicerTable'[SlicerColumn] ;BLANK());
      «Average Score»; [AvgScore];
      «Total Score»; [TotScore];
      BLANK ()

  7. Hi Maxim,

    Thanks for this post. I checked out Sam McKay’s post that you shared, but I find it limiting to only be able to use one measure at a time. Your method involves creating a “shy measure” for each calculated measure needed. I was wondering if you found a way to be able to create the same measure slicer, without having to duplicate each calculated measure as a shy measure. I’ve been trying to play around with it and use the switch function, but logically I don’t think it makes sense to have one field in the “values” part of the input to generate multiple measures on one chart. Have you found a way that does not require a shy measure for each calculated measure?


    • Maxim Zelensky
      August 25, 2017 - 21:58

      Hi Stephanie.
      No, currently I do not know the appropriate method, except if you’ll play with visual dependencies.
      What am I talking about: you create only one version of measure (that shy one), use it on other visuals, and set OnOff slicer to affect only one visual, so you can use shy measure on other visuals and calculations, not depending on OnOff slicer selection.


  8. Maxim,
    great post!

    Here’s another twist to the problem to make it more interesting: how to dynamically adjust the measure format based on selected measure.

    I’ve done lots of research and testing trying to pull that off but no cigar.
    Using SWITCH() in combo with FORMAT() was as close as I managed to get to a solution but it is limited in that it works for measure results displayed on a card but not in a chart

    Here’s a sample measure from a test model:

    ISFILTERED(‘xMeasure Sel'[Selected Metric]),
    SWITCH(VALUES(‘xMeasure Sel'[Selected Metric]),
    “Budgeted Hrs”,FORMAT([MM 1 T1000], “Standard”),
    “Earned Hrs”,FORMAT([MM 1 T1000], “Standard”),
    “% Comp”,FORMAT([MM 1 T1000], “Percent”)

    • Maxim Zelensky
      April 12, 2017 - 16:06

      Hi Igor!
      Really interesting task. Will think about…

    • Maxim Zelensky
      April 17, 2017 - 16:10

      Igor, after thinking a while I concluded that is is partially impossibe.
      One measure can have only one format, and, if you try to use FORMAT to change measure value’ appearance, you actually change measure format to the TEXT. So, it will work with Card visual, for example, but can’t work with time-series charts or other charts where you try to visualise measure values as lines, bars etc.
      So, I recommend you to use a separate measures (with the one desired format). There you’ll get one measure for all “Currency” formats, for example, one measure for a “Percent” format, etc. They should refer to the same slicer. Then place them all in a ‘Value’ area of a chart.
      I tried to combine SWITCH with my solution, or SWITCH with other built-in SWITCHes, but result is the same: one measure – one format, and this format is taken from top-level measure.
      The only issue that Y-axis (for example, on a bar chart) takes format from the first measure in the ‘Values’ list, and don’t change it when you change displayed measure.

Leave a Reply

Your email address will not be published / Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.