topic Re: Dynamically changing measures with in a line chart in DAX Commands and Tips

Dynamically changing measures with in a line chart

Anonymous — Thu, 05 Sep 2019 19:03:43 GMT

I have a handful of metrics, each of which have 3 Measures, current year, prior year and target.

so:

Metrics 1: CY_M1, PY_M1, Target_M1

Metrics 2: CY_M2, PY_M2, Target_M2

Metrics 3: CY_M3, PY_M3, Target_M3

Metrics 4: CY_M4, PY_M4, Target_M4

Metrics 5: CY_M5, PY_M5, Target_M5

so what I have are a total of 15 measures.

What we need from a business perspective is to have a multiple line chart that shows all the measures for a metric dynamically, with the metric being shown having the ability to be changed by a slicer.

i've tried a few different methods such as turning the measures into variables with in a new measure that would return all 3 variables, but this didn't work for me.

I also tried similar llogic but with a switch statement.

the problem with both is it only returned the first variable, not all three.

any help would be very much appreciated!

Re: Dynamically changing measures with in a line chart

sturlaws — Fri, 06 Sep 2019 09:28:34 GMT

Hi,

here is a strategy that migh work for you:

-Create a new table with only the name of your measures(does not have to be the name of the measure, but might be easier for your users). So table name = SelectMeasures, and name of the column = Measure.

-Create a new measure like this:

select measure =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( SelectMeasures[Measure] ) = [your measure name 1];
    SELECTEDVALUE ( SelectMeasures[Measure] ) = [your measure name 2];
    .
    .
    .
    .
    .
    BLANK ()
)

-Add the Measure-column in the legend field of a line chart.

-Add the measure [select measure] as value of the line chart.

-Add year/date to axis of the line chart.
-Create a slicer of the Measure-column.

Re: Dynamically changing measures with in a line chart

Anonymous — Fri, 06 Sep 2019 12:21:10 GMT

Hey thanks for the responce,

the problem with this is the same as the switch i already tried, with out multi selecting the slicer you can't put multiple measures onto the chart and all though that may seem easy enough for IT people unfortuantly that doesnt meet the business specs required.

What i ended up doing is kind of a workaround with bookmarks and putting different chart on different pages that all look the same so it appears like our clicking a button and only the chart is changing as far as the user is concerned.

Re: Dynamically changing measures with in a line chart

sturlaws — Fri, 06 Sep 2019 12:27:24 GMT

so what you want to do is show measures on groups of CY,PY and Targets?

Re: Dynamically changing measures with in a line chart

Anonymous — Fri, 06 Sep 2019 12:30:49 GMT

Yeah, so i need show the metric as whole which consists of those 3 measures as shown above

Re: Dynamically changing measures with in a line chart

sturlaws — Fri, 06 Sep 2019 13:33:18 GMT

ah, my bad, I should have understood that from you first post.

But if you use the strategy I suggested, and add new column, call it MeasureGroup, so the measures CY1, PY1 and T1 is group 1 and so on. Use this new column as values for a slicer. Perhaps check out the ChicletSlicer from Marketplace, which will give you more of a button-feel(just remember to turn off Multiple selection)