Forum Discussion
Problem with inactive relationships
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
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
))
)
10 Replies
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.
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!
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.
ThanksHarish M
Kindly give Kudos and accept it as solution if its solves your problemHi,
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
))
)
