Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

@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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
Super User

@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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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 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
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.