cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dancarr22
Resolver I
Resolver I

Using ALLEXCEPT to get value outside of current filters/context

Hello,

 

I am encountering difficulties using ALLEXCEPT.   Have also tried ALL and ALLSELECTED but none seem to work.

We have the following dataset:

SecurityDateAmount
XYZ12/31/2020468
XYZ3/31/2021657
XYZ6/30/2021389

 

The date column of this field joins to the date field in our date table - not sure if that impacts this.

What we need to do is get the MAX(Date) which is LESS THAN the given filters/context dates.

So, if filtered (and context) date range is 1/1/2021-3/31/2022 - we need to return date 12/31/2020 - which is 'outside' of the filtered date dataset.  So, need to remove any filters related to that date column.  
Currently have this:

StartingValueDate = VAR startDate = MIN(SecurityAmounts[Date])
RETURN CALCULATE(MAX(SecurityAmounts[Date]),   ALLSELECTED(SecurityAmounts[Date]), SecurityAmounts[Date] < startDate)

ALLSELECTED comes the closest but doesn't work when multiple securities are selected.  Basically, just need to remove the filter on the Date field for this measure.  Tried REMOVEFILTERS too but that did not work.  Appreciate any assistance which can be provided.

Thanks,
Dan
1 ACCEPTED SOLUTION
FreemanZ
Community Champion
Community Champion

A measure with the code below shall work:

 

StartingValueDate =
VAR MinDate = MIN (SecurityAmounts[Date])
RETURN
    MAXX (
        FILTER(ALL(SecurityAmounts[Date]), SecurityAmounts[Date] < MinDate),
        SecurityAmounts[Date]
)
 
FreemanZ_0-1668215245393.png

 

View solution in original post

2 REPLIES 2
CNENFRNL
Super User
Super User

Simple enough

CNENFRNL_0-1668236966332.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

FreemanZ
Community Champion
Community Champion

A measure with the code below shall work:

 

StartingValueDate =
VAR MinDate = MIN (SecurityAmounts[Date])
RETURN
    MAXX (
        FILTER(ALL(SecurityAmounts[Date]), SecurityAmounts[Date] < MinDate),
        SecurityAmounts[Date]
)
 
FreemanZ_0-1668215245393.png

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors