Filter and AllExcept

[SingSong]

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

[MartynRamsden]

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]

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

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

[SingSong]

@MartynRamsden unfortnately not, I am using an SSAS model and the measures are written in there so not visible in pbix. I will take some time later to duplicate using excel or something as I would really like to get this issue resolved.

Thanks

D