cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Filter only on the date slicer, but not the other visuals

Hello. I have a Card visual showing the number of rows in a fact table and would like the number to change depending on certain dates selected from a date slicer using the measure:

 

Counts = CALCULATE(countrows('Case Data'), ALLEXCEPT(Dates, Dates[Date]))

 

And this works fine. The problem I have is that I also have another visual (a Table) in the report which breaks down the respective 'Counts' by different Areas and counts their respective figures making up the overall total of the 'Counts' measure. However, when one of the Areas are clicked, this filters through and reduces the overall 'Counts' because it is then only showing the overall count for that particular Area (so it is filtering the count by the Area, and by dates selected). But I do not want the selected Area to filter the overall Count Measure outside of its own visual table, as i want to make an easy visual comparison between the sum and the parts.

I thought using ALLEXCEPT might have solved this but it has not done so. Any help would be greatly appreciated. Thank you.

Please see below, where the overall count has changed after the North Area is selected in the visual below:

Sample ScreenshotSample Screenshot

 

1 ACCEPTED SOLUTION

@SGP1 

Is the Area Name field from the Case Data table or is it from another table (dimension table for example)?

A much simpler option is just to turn off the interaction between the table visual and the card visual





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
Super User II
Super User II

@SGP1 

I can't test this but:

1) not sure why you need ALLEXCEPT for the card visual. If your Date Table has a relationship with your Case Data, you should be able to use 

Counts = COUNTROWS(Case data)

the date slicer will filter the rows in the case data down to the slicer selection. 

2) then, to ignore the Area filter, use:

Counts all areas = CALCULATE([Counts], ALL(Case data [Area]))

 

If you want it all in one measure:

Counts = CALCULATE(COUNTROWS(Case data), ALL(Case data [Area]))





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thank you for your reply Paul, it is much appreciated.

I was using the ALLEXCEPT function to try to prevent any clicks on the Areas visual from affecting the (overall) Counts total.

And just to confirm, the counts are working fine for any date selections.

Anyway, following your reply I have now changed the Counts measure to be

Counts = CALCULATE(COUNTROWS('Case Data'), ALL('Case Data'[Area Name]))
But when I click on the Areas visual it is still changing (reducing) the overall counts total as before.
Any further thoughts would be welcomed.
Thank you.

@SGP1 

Is the Area Name field from the Case Data table or is it from another table (dimension table for example)?

A much simpler option is just to turn off the interaction between the table visual and the card visual





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Thank you Paul, I'm fairly new to Power BI and wasn't too sure how the Interaction settings worked, but now you have mentioned it and I have tried it I have it working. Thank you very much.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors