Forum Discussion

Resolver I

5 years ago

Solved

ALLEXCEPT help needed

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
5 years ago
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

5 Replies

amitchandak
Super User
5 years ago
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
  Resolver I
  5 years ago
  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
    Super User
    5 years ago
    zivhimmel ,
    
    Try like
    
    calculate(
    
    AVERAGEX(ALL('Fact Sale'),'Fact Sale'[satisfaction]),filter(allselected('Dim Customer'),'Dim Csutomer'[City] = max('Dim Csutomer'[City]))
    )
v-deddai1-msft
Community Support
5 years ago
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

Related content