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
SingSong
Frequent Visitor

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 4
MartynRamsden
Solution Sage
Solution Sage

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.

Capture.JPG

 

 

 

 

 

 

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.

 

Capture1.JPG

 

Best regards,

Martyn

 

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

@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

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

@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

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.

Top Solution Authors