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 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.
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 = 60Hi 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 help
