Forum Discussion
Relationship Issue - Keep Filter ISsues.
I am using the below calculation for calculations of drivers.
Issue: When Region is not dragged in visual it is not filtering correct numbers and to fix this I tried adding KeepFilters which is slowing down the dashboard.
So basically the filter between Region Bridge and Dovico Table is not working without keep filters and upon using keep filters is slowing down the entire dashboard
Hi,
As per our understanding,your current measure relies heavily on:
- KEEPFILTERS(...)
- ALLEXCEPT(...)
When Region is not present in the visual, the filter context from your Region Bridge table is not fully propagating to the Dovico table.
So Power BI removes that context, and your calculation returns incorrect values.
Adding KEEPFILTERS forces it back, but hurts performance.Root Cause
This is a model relationship issue, not just a DAX issue.
From your model (image):
- You are using a bridge table (Region Bridge)
- Likely many-to-many or single-direction relationships
- Filter direction is not flowing properly to the fact table
Fix the Model Instead of DAX
- Go to Model View
- Check relationship between:
- Region Bridge → Dovico Monthly - AGG Table
- Ensure:
- Cross filter direction = Both
- Relationship is active
- Cardinality is correct (prefer 1 → Many)
Better DAX (Remove heavy KEEPFILTERS)
You can simplify your measure like this:
OD - ICC =
VAR yearmonth = VALUES('Date Table'[YYMM])
RETURN
DIVIDE(
CALCULATE(
SUM('Dovico Monthly - AGG Table'[ICC - Billable Cost (Formula)]),
'Dovico Monthly - AGG Table'[YearM] IN yearmonth
),
CALCULATE(
SUM('Dovico Monthly - AGG Table'[ICC - Billable Cost (Formula)]),
REMOVEFILTERS('Dovico Monthly - AGG Table'[Client_Name])
)
)Let the model handle filtering, not DAX.
Alternative Solution(If model can't change)
Use TREATAS instead of KEEPFILTERS:
CALCULATE(
SUM('Dovico Monthly - AGG Table'[ICC - Billable Cost (Formula)]),
TREATAS(VALUES('Region Bridge'[Region]), 'Dovico Monthly - AGG Table'[Region])
)This is:
- More efficient than KEEPFILTERS
- Explicitly applies filter from bridge → fact
Why KEEPFILTERS is slowing things
- It forces row-by-row filter evaluation
- Combined with VALUES() + IN → expensive
- Repeated in numerator & denominator → doubles cost
- Issue is due to filter propagation from bridge table
• KEEPFILTERS is masking a model design problem
• Fix by:
- Updating relationship direction (Both) OR
- Using TREATAS instead of KEEPFILTERS
Best practice:
If you need KEEPFILTERS to fix filtering, your model likely needs adjustment.Hope this helps.
Thanks!
6 Replies
Hi Revati25,
Issue is with your AllEXCEPT() function, which is removing filter from region and hence the filter from bridge table is also removed, Instead of using region as filter and ALLEXCEPT() function use REMOVEFILTERS() like below. also KEEPFILTERS() function is also making DAX heavy, try below DAX
OD - ICC =
VAR yearmonth = VALUES('Date Table'[YYMM])RETURN
DIVIDE(
CALCULATE(
SUM('Dovico Monthly - AGG Table'[ICC - Billable Cost (Formula)]),
'Dovico Monthly - AGG Table'[YearM] IN yearmonth,
TREATAS(VALUES('Date Table'[Date]), 'Dovico Monthly - AGG Table'[CustomDate]),
REMOVEFILTERS('Dovico Monthly - AGG Table'[Client_Name])
),
CALCULATE(
SUM('Dovico Monthly - AGG Table'[ICC - Billable Cost (Formula)]),
'Dovico Monthly - AGG Table'[YearM] IN yearmonth,
TREATAS(VALUES('Date Table'[Date]), 'Dovico Monthly - AGG Table'[CustomDate])
)
)🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!The logic would be
Per client Cost/ Whole Cost for that Region
Hi Revati25
ALLEXCEPT is removing Region filter context when Region is not present in the visual which is forcing to reapply it with KEEPFILTERS that impacts performance. Instead KEEPFILTERS, you should avoid over removing context by removing ALLEXCEPT or replace it with REMOVEFILTERS on specific columns.
so, I would suggest to fix this at the model level ensuring proper relationship of Region Bridge table to the fact table with correct cross filter direction
Hi,
As per our understanding,your current measure relies heavily on:
- KEEPFILTERS(...)
- ALLEXCEPT(...)
When Region is not present in the visual, the filter context from your Region Bridge table is not fully propagating to the Dovico table.
So Power BI removes that context, and your calculation returns incorrect values.
Adding KEEPFILTERS forces it back, but hurts performance.Root Cause
This is a model relationship issue, not just a DAX issue.
From your model (image):
- You are using a bridge table (Region Bridge)
- Likely many-to-many or single-direction relationships
- Filter direction is not flowing properly to the fact table
Fix the Model Instead of DAX
- Go to Model View
- Check relationship between:
- Region Bridge → Dovico Monthly - AGG Table
- Ensure:
- Cross filter direction = Both
- Relationship is active
- Cardinality is correct (prefer 1 → Many)
Better DAX (Remove heavy KEEPFILTERS)
You can simplify your measure like this:
OD - ICC =
VAR yearmonth = VALUES('Date Table'[YYMM])
RETURN
DIVIDE(
CALCULATE(
SUM('Dovico Monthly - AGG Table'[ICC - Billable Cost (Formula)]),
'Dovico Monthly - AGG Table'[YearM] IN yearmonth
),
CALCULATE(
SUM('Dovico Monthly - AGG Table'[ICC - Billable Cost (Formula)]),
REMOVEFILTERS('Dovico Monthly - AGG Table'[Client_Name])
)
)Let the model handle filtering, not DAX.
Alternative Solution(If model can't change)
Use TREATAS instead of KEEPFILTERS:
CALCULATE(
SUM('Dovico Monthly - AGG Table'[ICC - Billable Cost (Formula)]),
TREATAS(VALUES('Region Bridge'[Region]), 'Dovico Monthly - AGG Table'[Region])
)This is:
- More efficient than KEEPFILTERS
- Explicitly applies filter from bridge → fact
Why KEEPFILTERS is slowing things
- It forces row-by-row filter evaluation
- Combined with VALUES() + IN → expensive
- Repeated in numerator & denominator → doubles cost
- Issue is due to filter propagation from bridge table
• KEEPFILTERS is masking a model design problem
• Fix by:
- Updating relationship direction (Both) OR
- Using TREATAS instead of KEEPFILTERS
Best practice:
If you need KEEPFILTERS to fix filtering, your model likely needs adjustment.Hope this helps.
Thanks!
Hi Revati25,
Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to the krishnakanth240 and SamInogic for sharing valuable insights.
Could you please confirm if your issue has been resolved using the suggested approach? This will help other community members who may encounter similar scenarios.
Thank you for being part of the Microsoft Fabric Community.
