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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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