Problem with inactive relationships

[Alice_C]

Hi all,

Here is my data model :

I am trying to calculate the sum of Revenue per BusinessId (Business Table) with the folowing conditions :
- I have a slicer where I can select the DayId from Calendar Table

- I have a slicer where I can select the StateId from Geo Table
- I don't want all revenue, I must have a restriction on the CityId column from Sales Table : I only want the revenue of the city which Closure Date (Geo Table) is after Day Id (Calendar)

I tried seveal things using filter, summerize, treatas, but I don't manage to obtain the correct results

Any idea what I can try ?
Thank you in advance for your help !

Alice

[Alice_C · Accepted solution]

Hi,
I finally found the answer to my problem :

VAR SelectedDay = MAX ( Calendar[DayId] )

RETURN
CALCULATE(
SUM(Sales[Revenue] ),
FILTER(
Business,
Business[StateId] in values (Geo[StateId])
),
Sales[CityId] IN calculatetable(VALUES(Geo[CityId]), FILTER(
ALL(Geo),
Geo[ClosureDate]>SelectedDay
))
)

[HarishKM]

@Alice_C Hey,
You have to follow steps / suggestion to solve your problem.

• Create a measure in DAX to calculate the sum of Revenue per BusinessId.
• Use the FILTER function to restrict data from the Sales table based on CityId where the Closure Date from Geo Table is after the selected DayId in Calendar.
• Utilize the CALCULATE function to apply these filter conditions along with selected StateId.
• Use LOOKUPVALUE or RELATED to access Closure Date from Geo Table.
• Ensure slicers are correctly applied in the report to reflect selected DayId and StateId.

This approach will effectively narrow down the revenue calculations based on your specified conditions.

Thanks

Harish M

Kindly give Kudos and accept it as solution if its solves your problem

[alish_b]

Hey @Alice_C ,

I believe there could be better data model but since there is not enough business context or understanding of the table/data structure, it will be hard to suggest anything on that front.

Based on the screenshot only, is there any dependency on the many-to-many relationship that you have between Geo and Business? If not, please remove that relationship. Also, either activate or remove-and-rebuild the relationship between Geo and Sales to make the relationship active. Then try your measure.
If it still does not work, please add more context about the data and the connections. If possible, also share sample data in a format that can be copied and the expected result you would want from that sample.

Hope it helps!

[FarhanJeelani]

Hi @Alice_C ,

Yes. You can do this with a single measure

that:

gets the day selected in the Calendar slicer,
filters the Geo table to only cities whose ClosureDate is after that day,
and sums Revenue from Sales (which will flow through the CityId relationship to Geo).

If you have an inactive relationship between Calendar and Sales (common when you use two date columns), include USERELATIONSHIP for that path.

Recommended measure (assuming:

there is a direct relationship Geo[CityId] -> Sales[CityId]
and optionally Calendar[DayId] -> Sales[DayId] (which can be inactive)

DayId to Sales is active Revenue by Business := VAR SelectedDay = MAX ( 'Calendar'[DayId] ) RETURN CALCULATE ( SUM ( Sales[Revenue] ), 'Geo'[ClosureDate] > SelectedDay )

Option B: DayId to Sales is an inactive relationship (activate with USERELATIONSHIP) Revenue by Business := VAR SelectedDay = MAX ( 'Calendar'[DayId] ) RETURN CALCULATE ( SUM ( Sales[Revenue] ), USERELATIONSHIP ( 'Calendar'[DayId], Sales[DayId] ), 'Geo'[ClosureDate] > SelectedDay )

Please mark this post as solution if it helps you. Appreciate Kudos.

[Alice_C]

Hi @FarhanJeelani ,
Thank you for your answer, however that is not exactly what I want.
An example of dataset :
Business Table :

Calendar Table :

Geo Table :

Sales Table :

I forgot to explain that in Geo Table, StateId is the upper hierarchy of CityId.
Relationships between Geo and Sales and between Geo and Business are inactive.
Relationships between Sales and Calendat and between Sales and Business are active.

If I select 20250702 as DayId, 650 as StateId, I expect 30 as revenue because CityId 230 is closed and BusinessId 2 is affiliated to StateId 651 not 650.

I tried those below but I don't obtain the expected result :

Measure A
var SelectedDay = MAX('Calendar'[DayId])
return calculate ( sum(Sales[Revenue]), Geo[ClosureDate] > SelectedDay)
Result = 65

Measure B
var SelectedDay = MAX('Calendar'[DayId])
return calculate ( sum(Sales[Revenue]), Geo[ClosureDate] > SelectedDay, USERELATIONSHIP(Business[StateId], Geo[StateId]))
Result = 35

Measure C
var SelectedDay = MAX('Calendar'[DayId])
return calculate ( sum(Sales[Revenue]), Geo[ClosureDate] > SelectedDay, USERELATIONSHIP(Geo[CityId], Sales[CityId]))
Result = 60

[v-sshirivolu]

Hi @Alice_C ,

Thank you for providing the detailed model and example data, it helped clarify the requirements.

The main requirement is to calculate Revenue per BusinessId, but only for cities that remain “open” (where Geo[ClosureDate] > Calendar[DayId]). The slicers on Calendar[DayId] and StateId should still function as expected.

To do this, you can use a measure like the following:

Revenue (Open Cities) =
VAR SelectedDay = MAX ( Calendar[DayId] )
RETURN
CALCULATE (
SUM ( Sales[Revenue] ),
FILTER (
Sales,
RELATED ( Geo[ClosureDate] ) > SelectedDay
),
TREATAS ( VALUES ( Business[StateId] ), Geo[StateId] )
)

FILTER(Sales, …) ensures only sales from cities with ClosureDate > SelectedDay are included.

RELATED(Geo[ClosureDate]) retrieves the closure date from Geo for each Sales row.

MAX(Calendar[DayId]) gets the selected date from the Calendar slicer.

TREATAS(VALUES(Business[StateId]), Geo[StateId]) applies the StateId filter from Business to Geo, even if the relationship is inactive at that level.

[Alice_C]

Hi  @v-sshirivolu ,

Thank you for your help, I get what you mean.
Unfortunetely, your measure isn't working as I've an inactive relationship between Geo and Sales, hence related function isn't working.
I tried using lookupvalue function instead, but I got 60 as a result.
Thnak you in advance for your help

[v-sshirivolu]

Hi @Alice_C ,
Since the relationship between Geo and Sales on CityId is currently inactive, we need to activate it within the measure using USERELATIONSHIP.

Please see the measure below:

Revenue (Open Cities) =
VAR SelectedDay = MAX ( Calendar[DayId] )
RETURN
CALCULATE (
    SUM ( Sales[Revenue] ),
    FILTER (
        Sales,
        RELATED ( Geo[ClosureDate] ) > SelectedDay
    ),
    USERELATIONSHIP ( Sales[CityId], Geo[CityId] ),
    TREATAS ( VALUES ( Business[StateId] ), Geo[StateId] )
)

USERELATIONSHIP(Sales[CityId], Geo[CityId]) activates the inactive relationship for this calculation. RELATED(Geo[ClosureDate]) functions correctly because the relationship is now active within the measure. TREATAS applies the StateId filter from Business to Geo. FILTER ensures that only records where Geo[ClosureDate] is greater than SelectedDay are included.

[Alice_C]

Hi @v-sshirivolu ,
That doesn't work, I have the same error in the related function ,"Geo[ClosureDate] cannot be found"

[v-sshirivolu]

Hi @Alice_C ,

As RELATED continues to fail due to the inactive relationship, you can use LOOKUPVALUE as an alternative:

Revenue (Open Cities) =
VAR SelectedDay = MAX ( Calendar[DayId] )
RETURN
CALCULATE (
    SUM ( Sales[Revenue] ),
    FILTER (
        Sales,
        LOOKUPVALUE ( Geo[ClosureDate], Geo[CityId], Sales[CityId] ) > SelectedDay
    ),
    TREATAS ( VALUES ( Business[StateId] ), Geo[StateId] )
)

This approach allows you to match CityId directly, without relying on USERELATIONSHIP.

Additional considerations:

Verify that a relationship exists between Sales[CityId] and Geo[CityId].

Review the relationship direction and cardinality, as RELATED requires a many-to-one path.

For troubleshooting, you may want to use a small calculated table to validate the results from LOOKUPVALUE or RELATED.