Forum Discussion

AlejandroPCar's avatar
AlejandroPCar
Helper 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...
Need Help
v-sihou-msft's avatar
v-sihou-msft
Microsoft 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
Helper 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.