is there a way to let dax calculate measure of category based on 2 date tables?

[eliasayyy]

hello everyone!

i have 3 slicers

Category

Date

comparison date

table is connected to dimdate and inactive relationship with dimcomparedate

my measure is Calculate(Sum('Table'[total]))

if i select 2 categories.

sometimes my category is not in the selected date range but is available in the comapre date range 

how can i tell dax that if selected category has data in the selected date, use dimdate if not use comparison date.

example

category TV and Mobile

Tv has data in 2025 and 2024

Mobile has data only in 2024

dindate is filtered to 2025 while compare date is filtered to 2025,so i need total of 2025, however since mobile has no data in 2025, i need to use compare date for only mobile sonce tb has data in 2025 so we use dimdate range

thank you

[FBergamaschi]

I prefer to avoid giving answers when it is unclear what the issue is

Can you please share your pbix and/or give picyure of the model AND the issue from your pbix file?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[GrowthNatives]

Hi @eliasayyy , this is a classic “choose the right calendar per category” problem. The pattern is:

• iterate categories in the current filter context,

• for each category decide whether it has rows under the active date slicer,

• if yes use the active date relationship; if no, compute the value using the compare date relationship (made active with USERELATIONSHIP),

• then sum those per-category results.

Below is a compact, production-ready DAX measure that does exactly that. Paste it into Modeling → New measure and adjust column/table names to match yours.

Total by Preferred Date =
SUMX(
    VALUES( 'Table'[Category] ),            -- iterate each category visible in the current context
    VAR CurrCategory = 'Table'[Category]

    -- does this category have any rows using the active (regular) date slicer?
    VAR HasInActiveDate =
        CALCULATE(
            COUNTROWS( 'Table' ),           -- count respects the current page slicers (DimDate)
            KEEPFILTERS( 'Table'[Category] = CurrCategory )
        )

    VAR ValueInActiveDate =
        CALCULATE(
            SUM( 'Table'[total] ),         -- normal sum using active relationship to DimDate
            KEEPFILTERS( 'Table'[Category] = CurrCategory )
        )

    VAR ValueInCompareDate =
        CALCULATE(
            SUM( 'Table'[total] ),         -- sum but force relationship to DimCompareDate
            USERELATIONSHIP( 'Table'[DateKey], 'DimCompareDate'[DateKey] ),
            KEEPFILTERS( 'Table'[Category] = CurrCategory )
        )

    RETURN
        IF( HasInActiveDate > 0, ValueInActiveDate, ValueInCompareDate )
)

Why this works

• SUMX(VALUES(...)) creates a row for each category in the current filter context (respecting any Category slicer selection).

• CALCULATE(COUNTROWS('Table')) runs under the active date filters by default (DimDate slicer), because that relationship is active. If the count is > 0 then the category has data in the selected date range.

• USERELATIONSHIP(...) temporarily activates the inactive relationship to DimCompareDate, so ValueInCompareDate respects the comparison-date slicer.

Important 

• The measure uses KEEPFILTERS on the category to be explicit about the category filter; if your iteration is already providing the filter implicitly you can drop KEEPFILTERS, but it’s safer to keep it.

• If both active and compare date ranges are empty for a category, this returns BLANK (or 0 if you wrap with COALESCE(...,0)).

• Performance: SUMX over many categories can be expensive. If you have thousands of categories consider using SUMMARIZE/SUMMARIZECOLUMNS or pre-aggregating in Power Query. If only a few categories are selected by slicer, cost is low.

• If your data model has a separate dimension table for Category (e.g., DimCategory[Category]), use VALUES(DimCategory[Category]) instead of `VALUES('Table'[Category]) for cleaner semantics.

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]

[Zanqueta · Accepted solution]

hi @eliasayyy 

If I understood, you want to create a measure that dynamically chooses between two date relationships depending on whether the selected category has data in the primary date range. This is achievable in DAX by combining CALCULATE, USERELATIONSHIP, and conditional logic.

---

Proposed Solution

1. General Idea
   • Check if the category has data in the range defined by DimDate (active relationship).
   • If it does, use that relationship.
   • If not, activate the relationship with DimCompareDate and calculate accordingly.

Example Measure:

TotalDynamic =
VAR TotalDimDate =
    CALCULATE(
        SUM('Table'[Total]),
        USERELATIONSHIP('Table'[DateKey], 'DimDate'[DateKey])
    )
VAR TotalCompareDate =
    CALCULATE(
        SUM('Table'[Total]),
        USERELATIONSHIP('Table'[DateKey], 'DimCompareDate'[DateKey])
    )
RETURN
IF(
    NOT ISBLANK(TotalDimDate),
    TotalDimDate,
       TotalCompareDate

Important Considerations

• This logic works per category because the slicer context for Category is applied.
• If you have complex filters, you may need to use KEEPFILTERS or adjust the context.
• Performance: This approach runs two calculations per context, which can impact large models. If performance becomes an issue, consider optimising with helper tables or pre-calculated measures.

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.