- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
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):
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:
Desired Outcome:
What is wrong with my DAX?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
06-06-2024 07:38 AM | |||
02-05-2024 02:33 PM | |||
01-22-2024 11:20 AM | |||
09-12-2024 12:22 PM | |||
09-15-2024 08:24 PM |
User | Count |
---|---|
23 | |
11 | |
10 | |
9 | |
9 |
User | Count |
---|---|
17 | |
14 | |
13 | |
13 | |
12 |