Cross filter table to Line Chart but keep only some filters.

[DougMusic80]

Hello Power BI Community!

I am trying to filter a line chart by clicking on a table with data. Ideally what I am looking to achieve is the second image (keep the ID selected on the table and the full line with all the values and dates). I want to include the ID in the filter on the line chart AND all the dates with values (not just the date and value selected in the table). However, it is only showing the one date that is selected on the table. I thought I could use an ALL or an ALLEXCEPT on a measure to achieve this, but I am having no success. Must be something simple I am missing? Thank you very much for the help!

[Ritaf1983 · Accepted solution]

Hi @DougMusic80 
As a table is not a slicer the interactions with other visuals are in all contexts of specific rows:
id + date.
If you need filter-only id you should use a slicer.
From UX perspective you can put the slicer in some place close to the graph or on the graph.

Something like this:

If this post helps, then please consider Accepting it as the solution to help the other

members find it more quickly.

[Anonymous · Accepted solution]

Hi @DougMusic80 ,

By design, unfortunately, the current system does not support this function.

If you would like to suggest  feature improvements, you may vote the idea and comment here to improve this feature. It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous · Accepted solution]

Hi @DougMusic80 ,

By design, unfortunately, the current system does not support this function.

If you would like to suggest  feature improvements, you may vote the idea and comment here to improve this feature. It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[lmdt13_]

Hi @Anonymous , do you know if this function is supported now?  I have a situation that is almost exactly the same as the original poster. Just curious if this is possible now, thank you! 

[charles_g]

Hi @lmdt13_ , Hi @DougMusic80, 

I found a workaround. It requires creating a duplicate of the dimension table you want to keep unfiltered (the Dates table in your case) and a calculation group. 
First create a duplicate of your dates table:

__datesDetached = Dates

This table must remain unrelated to any other table. Use this new table in place of the date dimension in your second visual (the one you want to apply the cross-filter to). This way, any filter on the real Dates table, won' t affect this visual. At this stage the line chart should be flat since the x-axis has no relationship to your facts data points anymore. 

To capture the dates on the x-axis, we need __datesDetached[dates] to act as dates[dates]. This is done by creating a virtual relationship using TREATAS. TREATAS will propagate the unconnected dates on the x-axis to the proper dates table. The best way to do that is to use a calculation group to alter the underlying measure. 

Create a new calculation group useDetachedDates, with the following calculation item:

use detached dates = 
CALCULATE(
    SELECTEDMEASURE(),
    TREATAS(
        VALUES(__datesDetached[Date]),
        Dates[Date]
    )
)

If you're unfamiliar with TREATAS, check out this SQLBI article.

Now you just need to apply this calculation group as a filter to the second visual.

I hope this helps. 

[DougMusic80]

Hi Rita, I appreciate the post! However I am looking to achieve this without the slicer. It's part of the workflow to click on the current value in the table (for the id) then filter just line chart directly from the table depending on the id. I'm pretty sure there is a way to do this by writing a measure, I just cant figure it out. Thank you!!

[Ritaf1983 · Accepted solution]

Hi @DougMusic80 
Unfortunately, there are no other alternatives to achieve it, please refer to @v-tangjie-msft's response.

[Ritaf1983 · Accepted solution]

Hi @DougMusic80 
As a table is not a slicer the interactions with other visuals are in all contexts of specific rows:
id + date.
If you need filter-only id you should use a slicer.
From UX perspective you can put the slicer in some place close to the graph or on the graph.

Something like this:

If this post helps, then please consider Accepting it as the solution to help the other

members find it more quickly.