cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rcheskin
Helper I
Helper I

Use disconnected table Slicer to show/hide a target curve in a Line Chart and ignore other filters

I have a line chart with continuous data where users can select different products through various filters to appear as separate lines on the chart.  I want to add a Slicer that toggles the appearance of a Reference Curve on the line chart.  See blue dashed line and Reference slicer below:

LineChart_wRef.PNG

 

The structure of the data table (FR_Data) is below.  Each curve on the line graph shows data from a Label.  Brand, Model, and Side are blank for the Reference Curve.

 SampleData.PNG

 

I'm familiar with using disconnected tables to filter a measure on a separate table via SELECTEDVALUE in DAX.

SELECTEDVALUE.PNG

 

I'm struggling with how to set up the measure dervied from dBSPL so that the appearance of the Reference Curve is only determined by the ON/OFF slicer, i.e. the Reference Curve does not interact with any of the other Slicers.  If I append the Reference Curve data to the rest of the products so all data is in 1 table, the below measure works only if all of the products are selected:

 

 

dBSPL_Measure = if(
    'ASR Reference Toggle'[Selected Ref]=1,max(FR_Data[dBSPL]),
   if( 'ASR Reference Toggle'[Selected Ref]=0,CALCULATE(max(FR_Data[dBSPL]),KEEPFILTERS(FR_Data[Label]<>"ASR Reference"))
   ))

 

 

If a user filters the products with the slicers, then the Reference Curve will disappear, unless they select blank values, but I don't want users to have to do this.  Basically I want the On/Off slicer to show/hide the Reference Curve, regardless of how the other slicers have been filtered.  I can keep the data for the Reference Curve separate, combine it, etc...whatever needs to be done for a solution.

 

Link to sample .pbix with data below:

SampleData PBIX 

1 ACCEPTED SOLUTION
parry2k
Super User III
Super User III

@rcheskin see attached, I think that is what you are looking for, you need a disconnected table for slicers to make it work.

 

reference.gif

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

7 REPLIES 7
parry2k
Super User III
Super User III

@rcheskin it is pretty straightforward:

 

 

The following code appending the value of Reference Curve for whatever is already selected in the slicer

 

VAR __label = UNION ( VALUES ( 'Slicer Table'[Label] ), { "Reference Curve" } )

 

TREATAS function works as a slicer for unrelated tables, so basically whatever is selected in the slicer including the reference curve value filters the data table.

 

I hope this explanation makes sense, if you still need further clarification, let me know.

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User III
Super User III

@rcheskin see attached, I think that is what you are looking for, you need a disconnected table for slicers to make it work.

 

reference.gif

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

@parry2k Thanks very much!  This works.  I get the disconnected slicer table, but could you explain a little how your new measure works?  I'm not following how it is possible to UNION {"Reference" Curve} and the TREATAS.

 

dBSPL_Measure = 
/*
if(
    'Reference Toggle'[Selected Ref]=1,CALCULATE(max(FR_Data[dBSPL])),//
   if( 'Reference Toggle'[Selected Ref]=0,CALCULATE(max(FR_Data[dBSPL]),KEEPFILTERS(FR_Data[Label]<>"Reference Curve"))
   ))
*/
VAR __label = //VALUES ( 'Slicer Table'[Label] ) 
UNION ( VALUES ( 'Slicer Table'[Label] ), { "Reference Curve" } )
RETURN

IF ( MAX ( FR_Data[Label] ) = "Reference Curve" && [Selected Ref] = 0, 
    BLANK(),
    CALCULATE (
        MAX ( FR_Data[dBSPL] ),
        KEEPFILTERS 
        ( TREATAS ( __label, FR_Data[Label] ) )
    )
)

 

 

lbendlin
Super User III
Super User III

You can use BLANK() to your advantage.  You only need the disconnected table for the on/off slicer, and then set the reference curve measure to BLANK when the slicer is set to off. That will make it disappear from the chart.

 

 

dBSPL_Measure = if(SELECTEDVALUE('Reference Toggle'[Code])=0 && max(FR_Data[Label])="Reference Curve",BLANK(), max(FR_Data[dBSPL]))

 

@Ibendlin, but when the Toggle Code = 1 and I filter the products using slicers, the Target Curve will disappear (unless the corresponding value for the Target Curve is also selected in the slicer, but I don't want users to do that).  Like the measure I posted, your solution only works if there are no filters on the products.

Use the standard "Combine static and dynamic tables"  pattern. Put the reference curve data into the static table, then create a new UNION table that feeds the visual but feed the brand/product filters from the regular data table.

FR_Data = UNION('FR_Data No Ref',Reference)

 

lbendlin_0-1623543712181.png

 

The slicers won't filter anything, and if I connect the tables then I have the same problem as before.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors