AllExecpt Formula Questions

DAX DAX Calculations
1

Ultimate Beginners Guide to DAX

I have downloaded the attached files, one of them the excel fill understanding your customer,
i don’t understand the Measure Lifetime sales and the other group lifetime profit

i don’t understand the measure and Allexcept, i put the measure in a table with total sales and customer name
and i found that the two numbers for each customer are not the same,

Pls explain what the number for the lifetime measure reflect to?

2

I don’t see an excel file titled “Understanding Your Customer” in the resource pack for the Ultimate Beginners Guide to DAX, I also don’t see a measure called “Lifetime Sales” in the PBIX for that resource pack.
image

Can you point me to the module in the course where that measure is created?
Or, you may want to review the information on ALLEXCEPT in the knowledge base.
https://info.enterprisedna.co/dax-function-guide/allexcept/

1 Like
3

Hi here is the link to the course i mean https://portal.enterprisedna.co/courses/195681/lectures/3091957
your can check it Pls

Find attached the pack file i just downloaded again from the resources of the course Ultimate Beginners Guide to DAX and also a screenshot of the measure i meant , under measure group Customer Measure

Thanks,
Abeer Attia

RESOURCE PACK.zip (9.5 MB)

Capture
Capture1366×768 163 KB

4

This is because the measure ‘Lifetime Sales’ is affected by ‘Customer Names’ only.
While the measure ‘Total Sales’ is filtered by ‘Customer Names’ AND ‘Year’.
The filter on ‘Year’ is actief on alle pages.

image
image1327×830 79.3 KB

Hope this is helpful.

1 Like
5

thank you for pointing me to the correct resource pack - for some reason my link did not go to quite the same location :slight_smile:

Now, as to the problem with “Lifetime Sales” not equaling “Total Sales”
I believe the issue is due to the filter on “Year” in the filters pane:
image

“Lifetime Sales” will ignore the date filter - because that is what it is being told to do.

ALLEXCEPT is defined in the Microsoft docs as:

Removes all context filters in the table except filters that have been applied to the specified columns.

Here is the measure for Lifetime Sales (notice that ALLEXCEPT is referencing the Sales table as the table to REMOVE all context filters from, EXCLUDING the Customer Names column (which does not appear on the sales table, and I believe is an error that was introduced when the file was updated to a newer PBIX format).
Because Dates are on the Sales column, they are removed - so “Lifetime Sales” is behaving as it should in that instance.
However, it is NOT keeping the Customer Names filter, because that does not appear on the Sales table.

Lifetime Sales = 
CALCULATE( 
	[Total Sales] , 
        ALLEXCEPT( Sales , Customers[Customer Names] ))

I have added a new measure “Lifetime Sales 2” - which will still ignore the date filters, and now will keep the Customer Filters:

Lifetime Sales 2 = 
CALCULATE( 
	[Total Sales] , 
        ALL( Dates ),
        ALLEXCEPT( Customers , Customers[Customer Names] ))

or, if you want to have the formula filter by Customer Name, AND Date, you would just use the original Total Sales

and here is how all three measures perform, with no date filter on the page:
image

and, if I return the 3 year filter to the filter pane:

image

1 Like
6

Welcome to the forum @Abeer :slight_smile:

It’s great to know that you are making progress with your query.

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!

1 Like
7

Thanks Abdelablib

for your reply, i realy didn’t check this are at all.