Measure - Calculate average while excluding the rows corresponding to the slicer selection

[danisharoon1]

Hi there

I have a slicer for category, and a card on which I want to display the average rating. However while calculating the average rating I want to exclude rows corresponding to the category selected via the slicer. All other slicers should apply on the measure.

I found a solution that seems to work fine when I create it in the form of a table with filter interaction disabled. However when I put it in a card with the slicer enabled, the numbers are not matching up in the table and card when I try across category slicer selections.

Powerbi file is as follows: exhibit file 

[Wilson_]

Hi danisharoon1,

I'm confused. I downloaded your pbix and the value in your WithoutSelectedCat card match the table when I change the value of the category slicer. Your measure is also very similar to the way I would've written it.

What are you expecting differently?

[danisharoon1]

Hi Wilson

If you select Design in the category slicer, you may notice that the number of the measure in card and table is not matching up.

Thank you

[Wilson_ · Accepted solution]

Hi danisharoon1,

It does match up for me, but I realized I must have unfiltered the date slicer.

In any case, I think your problem is trying to work with one table and slicing by multiple columns in the same table. This article from SQLBI explains the issue very well. Your solution is to create dimension tables to use in your slicers if you want to leave both slicers up.

Dates are strongly advised to be a dimension table in most data models anyway. Let me know if you have follow up questions.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.