Forum Discussion

mp390988's avatar
mp390988
Post Partisan
1 year ago
Solved

Date parameters are not filtering report

Hi,

 

I have a paginated report where I have From and To dates set as parameters which come from the Calendar table.

Then I have my main report which contains a TradeDate field coming from my fact table.

My datasource is a semantic model where I can see the date field from my Calendar table is connected to the TradeDate field in my fact table via a 1-to-many relationship.

 

This is the dax I have in my paginated report:

EVALUATE CALCULATETABLE(
SUMMARIZECOLUMNS(
'TradesPlus'[TradeDate],
'TradesPlus'[ClientID],
'TradesPlus'[ClientName],
'TradesPlus'[BuyCCY],
'TradesPlus'[BuyAmount],
'TradesPlus'[SellCCY],
'TradesPlus'[SellAmount],
'TradesPlus'[GBPVolume],
'TradesPlus'[GBPRevenue]
),
'Calendar'[Date] >= DATEVALUE(@FromCalendarDate),
'Calendar'[Date] <= DATEVALUE(@ToCalendarDate)
)

But when I run my paginated report, I still see records where the TradeDate field from my fact table does not adhre to the from and start date i chose. For example, From Date = 01/04/2025 and End Date = 01/08/2025 but am seeing records where TradeDate = 01/03/2025 which falls outside of the date range.

Anyone have a clue what is going on here?

 

Thank You

@help
  • jaineshp's avatar
    jaineshp
    1 year ago

    Hey mp390988,

    Looking at your DAX query, I can see the issue. You're filtering on 'Calendar'[Date] but displaying 'TradesPlus'[TradeDate] - these are two different fields, and the relationship between them might not be working as expected in this context.

    Solution Steps:

    1. Direct Filter Approach Replace your current filter conditions with:

     

    EVALUATE
    CALCULATETABLE(
    SUMMARIZECOLUMNS(
    'TradesPlus'[TradeDate],
    'TradesPlus'[ClientID],
    'TradesPlus'[ClientName],
    'TradesPlus'[BuyCCY],
    'TradesPlus'[BuyAmount],
    'TradesPlus'[SellCCY],
    'TradesPlus'[SellAmount],
    'TradesPlus'[GBPVolume],
    'TradesPlus'[GBPRevenue]
    ),
    'TradesPlus'[TradeDate] >= DATEVALUE(@FromCalendarDate),
    'TradesPlus'[TradeDate] <= DATEVALUE(@ToCalendarDate)
    )

     

    2. Alternative Using FILTER Function If you need to maintain the Calendar table relationship:

     

    EVALUATE
    FILTER(
    SUMMARIZECOLUMNS(
    'TradesPlus'[TradeDate],
    'TradesPlus'[ClientID],
    'TradesPlus'[ClientName],
    'TradesPlus'[BuyCCY],
    'TradesPlus'[BuyAmount],
    'TradesPlus'[SellCCY],
    'TradesPlus'[SellAmount],
    'TradesPlus'[GBPVolume],
    'TradesPlus'[GBPRevenue]
    ),
    'TradesPlus'[TradeDate] >= DATEVALUE(@FromCalendarDate) &&
    'TradesPlus'[TradeDate] <= DATEVALUE(@ToCalendarDate)
    )

     

    3. Parameter Verification

    • Check if your parameters are passing correct date values
    • Verify the date format matches your regional settings
    • Test with hardcoded dates first to isolate the issue

    4. Relationship Check

    • Confirm the relationship between Calendar and TradesPlus tables is active
    • Ensure both date fields have the same data type
    • Check if there are any inactive relationships affecting the filter context

    The key issue is that SUMMARIZECOLUMNS with external filters on related tables can sometimes behave unexpectedly. Filtering directly on the fact table date field should resolve your problem.

     

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


    Best Regards,
    Jainesh Poojara | Power BI Developer

2 Replies

  • jaineshp's avatar
    jaineshp
    Memorable Member
    1 year ago

    Hey mp390988,

    Looking at your DAX query, I can see the issue. You're filtering on 'Calendar'[Date] but displaying 'TradesPlus'[TradeDate] - these are two different fields, and the relationship between them might not be working as expected in this context.

    Solution Steps:

    1. Direct Filter Approach Replace your current filter conditions with:

     

    EVALUATE
    CALCULATETABLE(
    SUMMARIZECOLUMNS(
    'TradesPlus'[TradeDate],
    'TradesPlus'[ClientID],
    'TradesPlus'[ClientName],
    'TradesPlus'[BuyCCY],
    'TradesPlus'[BuyAmount],
    'TradesPlus'[SellCCY],
    'TradesPlus'[SellAmount],
    'TradesPlus'[GBPVolume],
    'TradesPlus'[GBPRevenue]
    ),
    'TradesPlus'[TradeDate] >= DATEVALUE(@FromCalendarDate),
    'TradesPlus'[TradeDate] <= DATEVALUE(@ToCalendarDate)
    )

     

    2. Alternative Using FILTER Function If you need to maintain the Calendar table relationship:

     

    EVALUATE
    FILTER(
    SUMMARIZECOLUMNS(
    'TradesPlus'[TradeDate],
    'TradesPlus'[ClientID],
    'TradesPlus'[ClientName],
    'TradesPlus'[BuyCCY],
    'TradesPlus'[BuyAmount],
    'TradesPlus'[SellCCY],
    'TradesPlus'[SellAmount],
    'TradesPlus'[GBPVolume],
    'TradesPlus'[GBPRevenue]
    ),
    'TradesPlus'[TradeDate] >= DATEVALUE(@FromCalendarDate) &&
    'TradesPlus'[TradeDate] <= DATEVALUE(@ToCalendarDate)
    )

     

    3. Parameter Verification

    • Check if your parameters are passing correct date values
    • Verify the date format matches your regional settings
    • Test with hardcoded dates first to isolate the issue

    4. Relationship Check

    • Confirm the relationship between Calendar and TradesPlus tables is active
    • Ensure both date fields have the same data type
    • Check if there are any inactive relationships affecting the filter context

    The key issue is that SUMMARIZECOLUMNS with external filters on related tables can sometimes behave unexpectedly. Filtering directly on the fact table date field should resolve your problem.

     

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


    Best Regards,
    Jainesh Poojara | Power BI Developer

  • mp390988's avatar
    mp390988
    Post Partisan
    1 year ago

    HI jaineshp ,

    Thank you for your reply.

    It worked with option 1 Direct Filter Approach.
    Actually, I had thought of this solution as well but was (and still) getting the below error message and therefore I used to click cancel and not happen to get round to actually testing to see if it works.

     




    Thank Yoiu