Forum Discussion

AlejandroPCar's avatar
AlejandroPCar
Icon for Helper IV rankHelper IV
8 years ago

ALLEXCEPT Issue

Hi!, 

 

I've found the following issue with ALLEXCEPT function:


My formula  --->  Valores CIIU =
 VAR ValoresCIIU = CALCULATE( [Semana Valores], ALLEXCEPT( CIIU, CIIU[id_clase] ) )
RETURN
 ValoresCIIU

This formula does not work as I expect it should. The result is the same to my [Semana Valores]

measure and when I put it in a matrix for see the result, as you can see, both formulas do the same, so ALLEXCEPT is not working. I want the formula show me the CIIU result (in table would be the 111 value or 17,570,148) for every row of every company that belongs to its respective CIIU. 

 Here is my pbix file if it would be needed. https://1drv.ms/u/s!AtTnrgPUQzQCkFc5l7kMeLoe7z9X


Thanks a lot!

 

 

Need Help

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    AlejandroPCar

     

    In your scenario, your CIIU[id_clase] and [Empresa] fields are from different table. These two fields have no direct relationship. So when you put both feilds into matrix and drill down into one level, it can't make the calculation group on CIIU[id_clase] with ALLEXCEPT(). 

     

    Their "relationship" is actually described in your fact table "Estado_Resultados". So you should put Estado_Resultados[id_ClaseCIIU4F] into matrix and ALLEXCEPT() on this column. But I still can't figure out why the measure can't take your report filter "departmentno <> BLANK()". So I hard code the filter into the measure:

     

    Measure = CALCULATE( [Semana Valores], ALLEXCEPT( Estado_Resultados,  Estado_Resultados[id_ClaseCIIU4F] ),FILTER(Geografia,Geografia[departamento]<>BLANK()))

     

     

    • AlejandroPCar's avatar
      AlejandroPCar
      Icon for Helper IV rankHelper IV
      8 years ago

      Hi v-sihou-msft

       

      Thank you very much for your response.  It seems like it works like I want, but the problem is it does not work for me if the ALLEXCEPT filter come from the main table Estado_Resultados, because the filters I need for ALLEXCEPT are in  CIIU table. Is there a way to do that? I mean use the CIIU table as an argument of ALLEXCEPT, I saw examples in power bi pages where it could be donde. What I am doing wrong? 

       

      Thanks again for your answer.