Forum Discussion

Alice_C's avatar
Alice_C
Frequent Visitor
11 months ago
Solved

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...
Need Help
  • Alice_C's avatar
    Alice_C
    11 months ago

    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
    ))
    )

v-sshirivolu's avatar
v-sshirivolu
Community Support
11 months ago

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's avatar
Alice_C
Frequent Visitor
11 months ago

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's avatar
    v-sshirivolu
    Community Support
    11 months ago

    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's avatar
      Alice_C
      Frequent Visitor
      11 months ago

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

      • v-sshirivolu's avatar
        v-sshirivolu
        Community Support
        11 months ago

        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.