Forum Discussion

SingSong's avatar
SingSong
Frequent Visitor
6 years ago

Filter and AllExcept

Hi,

I am using a tabular model with measures to actual and plan as well as the variance. I use a Date table to display in a line chart the progress throughout this year by month.

I also use a table visual to show current month to date variance of actual vs plan by location.

 

In summary, I have one line chart visual with all the months of the year and two lines for actual and plan

I also have a single table visual with each location in the rows and the value is the current months variance of actual to plan

 

My Chart lines are based on these measures

Current Year = CALCULATE([Prod This Year Value],filter(DateDimension,DateDimension[Year Specificity]="This Year"))
Current Year Plan = CALCULATE([Prod PKG Plan Value],filter(DateDimension,DateDimension[Year Specificity]="This Year"))
 

My Table contains this measure

%MTY vs Plan = DIVIDE([Current Month to Yesterday],[Current Month to Yesterday Plan],0)-1
 
When I click on a location in the table visual it is filtering the information in the chart to the current month only, I would like to the filter to be only the location and all months of the year remain.
 
How do I prevent the table measure filtering out all the other months in the chart measures while keeping the location filter in place
 
Thanks
D
 

4 Replies

  • MartynRamsden's avatar
    MartynRamsden
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Hi SingSong 

     

    You've got a couple of potential solutions...

     

    To achieve it using DAX, simply add ALL to the FILTER argument in your your expression:

     

    Current Year =
CALCULATE (
    [Prod This Year Value],
    FILTER ( 
        ALL ( DateDimension ), 
        DateDimension[Year Specificity] = "This Year" 
    )
)

     

     

    Or, you could edit the interation between the table and line chart visuals to prevent any cross highlighting.

     

    To do this, click on the table visual, then in the 'Format' toolbar, click the 'Edit Interations' button.

     

     

     

     

     

     

    You'll then see two icons appear at the top right of your line chart visual.

    Select the circle to prevent selections in the table affecting the line chart.

     

     

    Best regards,

    Martyn

     

    If I answered your question, please help others by accepting it as a solution.

    • SingSong's avatar
      SingSong
      Frequent Visitor
      6 years ago

      MartynRamsden  Thanks for the reply, unfortunately this is a solution I have already tried but tried again in the hope I had done something wrong. when implementing this solution the value for Current Year becomes the same for all months and it has no impact to the problem of the chart data being filtered to a single month when clicking on the table. I don't want to remove the interaction between the table and the chart because I do want clicking on the table to filter the chart to the the selected location.

       

      thanks

      D

      • MartynRamsden's avatar
        MartynRamsden
        Icon for Solution Sage rankSolution Sage
        6 years ago
        Hi SingSong

        Are you able to share a copy of your pbix? Be sure to remove any sensitive data before you do.

        Best regards,
        Martyn