CROSSFILTER in DAX not giving the desired result

[mp390988]

Hello,

@DataNinja 

I have the following data model:

And I have the below visual in my report:

The defintion of my MonthlyTarget measure is:

MonthlyTarget = CALCULATE(
    SUM(RevenueTargetsTbl[Target]),
    CROSSFILTER(
        CalendarTbl[Date],
        TradesTbl[TradeDate],
        Both
    )
)

But there are a couple of issues:
There shouldn't be any blanks in the visual for MonthlyTargets as we have supplied targets across all months.

Here is a glimpse of the source file for Targets and as you can see, Derivatives has a target of 166k for Jan 2025.

But I think I know why is it showing empty in my visual is because the relationship between my CalendarTbl and TradesTbl is defined on the TradeDate field and if I filter my TradeTbl by the TradeDate field = 01/01/2025 I see no trades for dealer group = Derivatives. 

The issue is that my TradesTbl is on a different granularity i.e the lowest grain is trade date whislt my RevenueTargetsTbl is on the monthly grain, if that makes sense? So basically, my CROSSJOIN is trying to do a computation on two tables with different granularity of data.

Does this make sense and can anyone please advise?

Thank You

[mp390988]

Just to make it very clear, I am using DealerGroup field from the my trade table:

Which gives this me incorrect results when i drop the MonthlyTargets measure but the correct results for actual revenue

If I swap the dealer group field from the TradesTbl with the dealer group from the RevenueTargetsTbl then I get the correct results for the measure MonthlyTargets but now the actual revenues are incorrect:

[jaineshp · Accepted solution]

Hey @mp390988,

Looking at your model structure, the issue stems from filter context not properly flowing between your TradesTbl and RevenueTargetsTbl when using DealerGroup as a filter.

Root Cause Analysis:

• When you use TradesTbl[DealerGroup], the filter context only affects the trades data
• RevenueTargetsTbl remains unfiltered because there's no direct relationship path
• Calendar table acts as a bridge but doesn't carry DealerGroup context between the fact tables

Recommended Solution:

1. Create a DealerGroup dimension table
   • Extract unique DealerGroup values: DealerGroupDim = DISTINCT(UNION(VALUES(TradesTbl[DealerGroup]), VALUES(RevenueTargetsTbl[DealerGroup])))
   • This ensures all dealer groups from both tables are captured
2. Establish proper relationships
   • Remove any existing DealerGroup relationships
   • Connect DealerGroupDim[DealerGroup] to TradesTbl[DealerGroup] (One-to-Many)
   • Connect DealerGroupDim[DealerGroup] to RevenueTargetsTbl[DealerGroup] (One-to-Many)
   • Maintain existing calendar relationships
3. Update visual configuration
   • Use DealerGroupDim[DealerGroup] in your matrix rows instead of fields from fact tables
   • Keep existing measures unchanged: SUM(TradesTbl[GBPRevenue]) and SUM(RevenueTargetsTbl[Target])
4. Verify filter propagation
   • Both ActualRevenue and MonthlyTarget measures will now respond to the same DealerGroup filter
   • Calendar filters will continue working as expected across both fact tables

This approach follows standard dimensional modeling practices and resolves the filter context issue without requiring complex DAX workarounds.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[johnt75]

Rather than using the trade date column in your matrix use a column from your date table, e.g. MonthYear. This not only removes the need to use CROSSFILTER at all but it also means that a row will show up in the matrix when there is valid data from either trades or targets.

[mp390988]

@johnt75 - I am using MonthYear in my matrix

[johnt75]

In that case you don't need the CROSSFILTER and you should be able to use

MonthlyTarget = SUM(RevenueTargetsTbl[Target])

[mp390988]

But the values are not correct when I use :

MonthlyTarget = SUM(RevenueTargetsTbl[Target])

For example, this is the source for monthly targets and you can see the value should be 166k for delaer group = Derivatives and MonthYear = Jan 2025 but my visual above is showing 1,556,667. What my visual is doing is taking the sum across all different dealer groups for Jan 2025 and not filtering on dealer group.

[jaineshp]

Hey @mp390988,

Instead of using CROSSFILTER, build a bridge on month-level granularity. Here’s a step-by-step fix:

1. Create a new column in CalendarTbl:

CalendarMonth = EOMONTH(CalendarTbl[Date], 0)

This gives you the month-end date, e.g. 31-Jan-2025 — consistent with monthly data.

2. Make sure RevenueTargetsTbl[Month] matches that format

If it doesn't, create:

TargetMonth = EOMONTH(RevenueTargetsTbl[Month], 0)

3. Relate CalendarTbl[CalendarMonth] to RevenueTargetsTbl[TargetMonth]

• Remove the old crossfilter logic.

• Create a proper 1:* relationship based on this month-level column.

4. Simplify your measure:

MonthlyTarget = SUM(RevenueTargetsTbl[Target])


Now Power BI will naturally use the CalendarTbl month context to fetch targets — no CROSSFILTER needed.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer