Reply
manosjxmaah
Frequent Visitor

Filter with AllEXCEPT and two Selection Dates Not working as Excepted

Hello!

 

I have a page that contains two dropdown filters and a matrix. The purpose of the page is for the user to select a year in one filter and another, different, year in the other filter. Afterwards, I calculate the percentage difference of a measure in the matrix.

 

My model is this:

manosjxmaah_1-1715860080953.png

The one filter is on DateTable and the other on CompareDates.

 

My matrix contains a hierarchy on the 04 Reg table and a hierarchy on the DateTable (uQuarterText and then uMonthName):

manosjxmaah_2-1715861155812.png

 

 

I want to calculate the average days a month has (which is contained in MonthTable) in the chosen year of CompareDates, but only when Avg Availability in Month > 0 (which is contained in Units table).

 

The code I am using is this, but all I get is a matrix with the same values all over:

 

 

VAR avgDaysTest = 
        CALCULATE(
            AVERAGE(MonthTable[Days]),
            REMOVEFILTERS(DateTable[Year With Current]),
            REMOVEFILTERS(DateTable[uYear]),
            compYear = MonthTable[_Year],
            FILTER(ALLEXCEPT(Units, DateTable[QuarterText], DateTable[uQuarter], DateTable[uMonthName], DateTable[uMonth],'04 Reg'[Reg], '04 Reg'[Abbr Group],'04 Reg'[Jet-Prop]), 
            Units[Avg Availability in Month] > 0 && compYear = Units[_Year])
         )​

 

 

I have also tried removing the ALLEXCEPT part but then I get a blank matrix.

When I remove the FILTER part altogether, I get a matrix with values, but not filtered correctly of course.

In other measures where I don't need to remove the effects of DateTable, the below code works fine:

 

 

CALCULATE(
        AVERAGE(MonthTable[Days]),
        FILTER(Units, Units[Avg Availability in Month] > 0)
    )

 

 

 Here is a sample pbi file.

Also some screenshot of some data and what i want the outcome to be. I have a simplified version with only reg in the rows of the matrix:

manosjxmaah_0-1715931286287.png

Desired Outcome:

manosjxmaah_1-1715931544928.png

What is wrong with my DAX?

2 REPLIES 2
lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Hello,

I added screenshots and a sample pbi file in topic.
Here is a link to download the sample file.

If something else is needed please inform me.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)