Forum Discussion

ITManuel's avatar
ITManuel
Responsive Resident
3 years ago
Solved

Cannot understand why ALLEXCEPT not working

Hi,   I have the following data model:                             and the following report:                 "Invoiced" is a measure calculated in the AInvoiceFl...
Need Help
  • Barthel's avatar
    Barthel
    3 years ago

    ITManuel 

    The ALLEXCEPT function removes only the filters from the table specified as the first argument (i.e. 'ProjectFilter'). Using ALLSELECTED (), where no table is specifically referenced, all inner filter context is removed (including the filters from the 'AInvoices' table). 

    I would avoid using ALLEXCEPT in measures anyway as much as possible. Please read this article for more information: https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/ 

Barthel's avatar
Barthel
Solution Sage
3 years ago

ITManuel 

The ALLEXCEPT function removes only the filters from the table specified as the first argument (i.e. 'ProjectFilter'). Using ALLSELECTED (), where no table is specifically referenced, all inner filter context is removed (including the filters from the 'AInvoices' table). 

I would avoid using ALLEXCEPT in measures anyway as much as possible. Please read this article for more information: https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/ 

ITManuel's avatar
ITManuel
Responsive Resident
3 years ago

Hi Barthel ,

 

thank you, this article helped me a lot.

 

After trying a bit, this measure is now working:

 

TotalInvoicedperProject = 
CALCULATE (
    [Invoiced],
    REMOVEFILTERS ( AInvoices ),
    SUMMARIZE ( AInvoices, Projects[PROJECTCODEMASTER] ) 
)

 

This is the result:

 

 

 

 

 

 

 

 

A had to Summarize AInvoices over Projects[PROJECTCODEMASTER] since invoices from different [PROJECTCODE]'s in the AInvoices table can belong to the same project in the ProjectFilter table.

 

Thank you & Best regards