Forum Discussion

mbowler's avatar
mbowler
Icon for Helper I rankHelper I
2 years ago
Solved

Adding 2nd line to line chart based on same table (but different filter context)

Hi,

I have a requirement to add a (selectable) comparison line to an event sales timeline chart below:

The page this visual is on has a drop-down slicer that chooses the main event (as shown above) and also has many other visuals showing data about that event. There are 2 things I need help with:

  1. How to provide a method for the user to select the comparison event (just for this chart) 
  2. How to add the sales data to the above chart for the comparison event

I've been looking into both things:

1.

a. I tried adding another slicer with the event list column to the page but this obviously followed the main slicer and interacted with the other visuals. I tried to decouple it with Edit interactions but this was very laborious because of the need to decouple it from every other visual on the page (there's 20 or more). And even then the result was that both slicers where affecting the filter context so no data was being shown on the visuals.

b. I have considered creating a separate (hidden) page where there would be a slicer to select the comparison event, then use bookmarks/page navagation and buttons to switch between the main page or set the comparison event page.

2. 

a. Not really sure how to get two sets of filtered data from the same table to show on the same chart. I'm wondering if there a way using measures and SELECTEDVALUE, ISFILTERED, REMOVEFILTERS, ALLEXCEPT etc

b. But not sure how I access the selected value in the comparison slicer. I wondered about creating a duplicate calculated column of the event name (eg called "CompEventName") such that the main event would be what was selected in the "EventName" column and the comparison event would be what was selected in the "CompEventName" column. 

 

Any help on this would be much appreciated.

Need Help

2 Replies

    • mbowler's avatar
      mbowler
      Icon for Helper I rankHelper I
      2 years ago

      Fantastic parry2k, just what I needed! This community is great.

       

      Just to help those who might read this later, here's what I did to solve my problem:

      1. Created a new (disconnected) table with just the list of the Event names: CompEventsList = VALUES(AllPerformances[EventName])
        • This allowed me to have a second slicer on the same page with the same set of Event names but completely independent of the slicer that is controlling the rest of the visuals on the page.
      2. Created a new measure that uses the same measure as in the line chart for the main event but changing the conext to the second table using TREATAS:
        • Comparison Event Running Total = CALCULATE( [Main Event Running Total], TREATAS( VALUES( CompEventsList[EventName] ), AllPerformances[EventName] ))
      3. Add the measure above to my line chart