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

HarishKM's avatar
HarishKM
Super User
11 months ago

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.


Thanks

Harish M

Kindly give Kudos and accept it as solution if its solves your problem