Reference lines on demand

[laciodrom_80]

Hi,

I've got a report with a line chart, I wish add to my report a reference line on demand: in particular I'd like to have a filter which specifies some reference lines (max - min - mean, etc.) so that when user clicks one of these the relative reference line is displayed into the chart, is it possible? 

Thanks in advance for any hint!

[v-haibl-msft]

@laciodrom_80

Vvelarde’s solution should be correct. You can also try to use the Min/Max/Average Line in analytics pane as below.

Best Regards,

Herbert

[laciodrom_80]

@v-haibl-msft unfortunatelly reference lines can't be shown/hidden on demand with this solution 

[Vvelarde · Accepted solution]

hi @laciodrom_80

First create a table with a column with Max, Min , Average ...Use this to show a slicer to select the calculate formula of the line.

Next, create a measure to calc accord to slicer selected.

LineCalc =
IF (
    VALUES ( Table1[Value] ) <> BLANK (),
    IF (
        HASONEVALUE ( Table2[LineType] ),
        SWITCH (
            VALUES ( Table2[LineType] ),
            "Max", CALCULATE ( MAX ( Table1[Value] ), ALLSELECTED ( Table1 ) ),
            "Min", CALCULATE ( MIN ( Table1[Value] ), ALLSELECTED ( Table1 ) ),
            "Average", CALCULATE ( AVERAGE ( Table1[Value] ),ALLSELECTED ( Table1 ) )
        )
    ),
    BLANK ()
)

[laciodrom_80]

Hi  @Vvelarde,

thanks for your suggestion, only twoclarification:

How are related the slicer selection and the LineCalc measure? Does Table2[LineType] in switch statement represent the current selection in the slicer?

How can I add the LineCalc measure to my chart?

Here my chart without reference lines

I should add LineCalc measure to "Valori" field (Spot Series Values is the measure which draws the chart lines), but if I drag and drop LineCalc measure to "Valori" field it overwrites Spot Series Values 

 Thanks a lot!

[v-haibl-msft]

@laciodrom_80

Just upload a simple .pbix file here for reference which follow the solution provided by Vvelarde.

If you have legend in your line chart, as Vvelarde said, you cannot have more than one value in the Values field now. Please vote this idea here.

Best Regards,

Herbert

[laciodrom_80]

Thanks guys!

@v-haibl-msft  idea just voted!  

[Vvelarde]

@laciodrom_80

How are related the slicer selection and the LineCalc measure? Does Table2[LineType] in switch statement represent the current selection in the slicer?

Yes. able2[LineType] represent the current selection in slicer. The slicer is the column of tha table with the line types.

How can I add the LineCalc measure to my chart?

If you have a legend this doesnt' work. When you use a legenf only Works with 1 line. 

[Greg_Deckler]

Not to my knowledge.