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

alish_b's avatar
alish_b
Super User
11 months ago

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!