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
Basdo
Frequent Visitor

Keep Filter Selection within ALLEXCEPT

Hi,

 

I have Data in this format:

 

DateNameSales
01.01.2022A10 €
02.01.2022A10 €
02.01.2022B100 €
03.01.2022A10 €
03.01.2022B100 €
04.01.2022A10 €
04.01.2022B100 €
04.01.2022C

1000 €

 

Now I want to build the running sales total per Name and the user should be able to select more than one names.

My current DAX formula is:

 

Sales running total =
CALCULATE(
    [Sales],
    FILTER(
        ALL(Table), [Date] <= MAX([Date])
    )
)

This is working when all names are selected but not when the user is for example only selecting names A and B as the ALL filter is removing the name filter context completely.

I also tried this formula:

CALCULATE(
    [Sales],
    FILTER(
        ALLEXCEPT(Table, Table[Names]), [Date] <= MAX([Date])
    )
)

But then the totals for each names are calculated separately and the outcome is not a total over all names as wanted.
 
I somehow need to remove the name filter to be able to calculate the totals but in the same time keep the user selection.
How do I do that?
1 ACCEPTED SOLUTION
lukiz84
Memorable Member
Memorable Member

Don't use ALLEXCEPT, use ALLSELECTED

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

One of ways to achieve this is to use column in the measure, not the whole table.

 

Jihwan_Kim_0-1664889357792.png

 

 

Sales running total: =
CALCULATE (
    [Sales:],
    FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] <= MAX ( 'Table'[Date] ) )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


lukiz84
Memorable Member
Memorable Member

Don't use ALLEXCEPT, use ALLSELECTED

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.