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 ,
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.