ALLEXCEPT help needed

[zivhimmel]

Hi,

I'm trying to use Allexcept with a column on a dimension table and it looks to be ignored.

Let's say I have a sales fact table. Every record is for a sale and there's a customer satisfacion score.

Then we have a customer dimension. So there's 1:many relationship between fact sales and dim customer, using customer_id.

One of the columns in the customer dimension is city.

In a report page, I want to be able to filter down to a specific customer, see all of their sales, and show, side by side, the customer's

avg. satisfaction vs. the avg. satisfaction of customers from their city.

I tried something like the following but the city seems to be ignores and I just get avg. satisfaction accross all sales

avg_satisfaction_Same_city=

calculate(

AVERAGEX(ALL('Fact Sale'),'Fact Sale'[satisfaction]),ALLEXCEPT('Dim Customer','Dim Csutomer'[City])

)

Any idea?

Thanks.

[v-deddai1-msft · Accepted solution]

Hi @zivhimmel ,

Would you please try to use the following measure:

avg_satisfaction_Same_city= CALCULATE(AVERAGE('Fact Sale'[satisfaction]),ALLEXCEPT('Dim Customer','Dim Csutomer'[City]))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@zivhimmel , All except, mean all filter other than what you mentioned will be ignored.

refer: https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

[zivhimmel]

Thanks @amitchandak . This is exactly what I want. I want to city of the customer to be respected (not ignored), but the customer filter to be ignored. I want to see all customers records from the same city as my customer. Happy to do it any other way, doesn't have to be ALLEXCEPT. Thanks.

[amitchandak]

@zivhimmel ,

Try like

calculate(

AVERAGEX(ALL('Fact Sale'),'Fact Sale'[satisfaction]),filter(allselected('Dim Customer'),'Dim Csutomer'[City] = max('Dim Csutomer'[City]))
)

[zivhimmel]

Thanks again. It doesn't work however.

It returns an avg of all values, ignoring all filters.