Forum Discussion
Problem with inactive relationships
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
))
)
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
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.
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 helpHi 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.
Hi v-sshirivolu ,
That doesn't work, I have the same error in the related function ,"Geo[ClosureDate] cannot be found"