Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ITManuel
Responsive Resident
Responsive Resident

Cannot understand why ALLEXCEPT not working

Hi,

 

I have the following data model:

Data model.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

and the following report:

Report.JPG

 

 

 

 

 

 

 

 

"Invoiced" is a measure calculated in the AInvoiceFlows table, "Paid" is a measure calculated in the CashIN table and "Pay delay" a measure in the "AInvoices" table. 

Now everything works fine, except the "Pay delay" on the total level per project --> 514 d is the sum of all delays from the individual invoices, which should be replaced by a weighted delay considering the weight of each indicidual invoice.

This is the calculation I would like to perform:

Calculation.JPG

 

 

 

 

 

 

 

 

In order to calculate the weighting of each invoice, I would need the total invoiced amount per project for each individual invoice related to the specific project. In the case above the 1.988.221@ for each individual invoice in order to perform the weighting.

 

I tried 

TotalInvoicedperProject = 
    CALCULATE (
        [Invoiced],
        ALLEXCEPT ( ProjectFilter, ProjectFilter[FullProjectName] )
)

and several other variations  but I'm achieving only this:

 

Report.2JPG.JPG

 

 

 

 

 

 

 

I'm stucked on this can currently not figure out how to achieve the 1.988.221€ for each line in the above report.

 

Any help is much appreciated.

 

Thanks in advance

1 ACCEPTED SOLUTION

@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/ 

View solution in original post

6 REPLIES 6
Barthel
Solution Sage
Solution Sage

Hey @ITManuel

Not sure, but maybe give this a try. Instead of using ALLEXCEPT, try using ALLSELECTED and VALUES. 

TotalInvoicedperProject = 
    CALCULATE (
        [Invoiced],
        ALLSELECTED ( ProjectFilter ),
        VALUES ( ProjectFilter[FullProjectName] )
)

This removes all filters for the ProjectFilter table within the visual itself (ALLEXCEPT), and keeps the project filter trough VALUES.

ITManuel
Responsive Resident
Responsive Resident

Hi @Barthel ,

unfortunately this is not working either. Following the report with your proposed measure. 

Report.3JPG.JPG

 

 

 

 

 

 

Should it be relevant, this is the [Invoiced] measure in the AInvoiceFlows table. 

Invoiced = 
VAR _T1 =
    FILTER ( AInvoiceFlows, RELATED ( Customers[INTERCOMPANY] ) = "NO" )
VAR _Result =
    SUMX (
        _T1,
        IF (
            AInvoiceFlows[AInvoicesHeader.DOCCURRENCY] = "EUR",
            AInvoiceFlows[VALUENETDC],
            IF (
                AInvoiceFlows[AInvoicesHeader.TARGETCURRENCY] = "EUR",
                DIVIDE (
                    AInvoiceFlows[VALUENETDC],
                    AInvoiceFlows[AInvoicesHeader.XCHANGERATETC]
                ),
                VAR _DocCurrency = AInvoiceFlows[AInvoicesHeader.DOCCURRENCY]
                VAR _OrderDate =
                    RELATED ( AInvoices[INVOICEDATE] )
                VAR _FindDateWithData =
                    CALCULATE (
                        MAX ( XChangeRates[DATEXCR] ),
                        CALCULATETABLE (
                            LASTNONBLANK ( XChangeRates[DATEXCR], MAX ( XChangeRates[XCHANGERATES] ) ),
                            XChangeRates[MAINCURRENCY] = "EUR"
                                && XChangeRates[FOREIGNCURRENCY] = _DocCurrency
                                && XChangeRates[DATEXCR] <= _OrderDate
                        )
                    )
                RETURN
                    DIVIDE (
                        AInvoiceFlows[VALUENETDC],
                        LOOKUPVALUE (
                            XChangeRates[XCHANGERATES],
                            XChangeRates[DATEXCR], _FindDateWithData,
                            XChangeRates[MAINCURRENCY], "EUR",
                            XChangeRates[FOREIGNCURRENCY], AInvoiceFlows[AInvoicesHeader.DOCCURRENCY]
                        )
                    )
            )
        )
    )
RETURN
    _Result

@ITManuel 

Does the 'INVOICECODE' in the matrix come from the 'AInvoices' table? In that case you could try the following:

 

TotalInvoicedperProject = 
    CALCULATE (
        [Invoiced],
        ALLSELECTED (),
        VALUES ( ProjectFilter[FullProjectName] )
)

 

ITManuel
Responsive Resident
Responsive Resident

Yes, the 'INVOICECODE' in the matrix comes from the 'AInvoices' table.

 

Your proposed code produces the following result.

Report 4JPG.JPG

 

 

 

 

 

 

 

 

The total invoiced amount of 1.988.221€ of the project 0JA00000000KE2 which come from the outer filter context is now presumably shown for each invoice of the AInvoice table. 

 

I still don't get why 

TotalInvoicedperProject = 
    CALCULATE (
        [Invoiced],
        ALLEXCEPT ( ProjectFilter, ProjectFilter[FullProjectName] )
)

which should remove any filter but keep the Filter of ProjectFilter[FullProjectName] is not working and not removing the filter of AInvoices[INVOICECODE] column in the matrix in which ProjectFilter[FullProjectName] and  AInvoices[INVOICECODE]  are used in the rows.

@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
Responsive Resident
Responsive Resident

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:

 

Report 5JPG.JPG

 

 

 

 

 

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.