Forum Discussion

zivhimmel's avatar
zivhimmel
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 sco...
Need Help
  • v-deddai1-msft's avatar
    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

zivhimmel's avatar
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's avatar
    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]))
    )

    • zivhimmel's avatar
      zivhimmel
      Resolver I
      5 years ago

      Thanks again. It doesn't work however.

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