Forum Discussion
Measurements with filter from a different tables
Hi SteenSoernesen ,
Instead, you can use LOOKUPVALUE() in your measure to determine if the station is a measuring point (Maalepunkt = TRUE). For example -Canceled_Events :=
CALCULATE(
COUNTROWS(Fact_Events),
Fact_Events[Status] = "aflyst",
LOOKUPVALUE(
Dim_Stationer[Maalepunkt],
Dim_Stationer[TogNr], Fact_Events[TogNr],
Dim_Stationer[Stop navn], Fact_Events[Stop navn]
) = TRUE()
)This formula matches the train number and station name from the fact table to the dimension table, filtering for rows where Maalepunkt is true. You can apply this approach to other statuses such as forsinket, planmaessig, and others.
Hello Sreeteja
I think you might have found a fault in out Automatically generated fact_event. There are no StationId, only the Station Name.
I have a feeling that might have been missed by the people design the system.
Hi SteenSoernesen ,
Instead, you can use LOOKUPVALUE() in your measure to determine if the station is a measuring point (Maalepunkt = TRUE). For example -
Canceled_Events :=
CALCULATE(
COUNTROWS(Fact_Events),
Fact_Events[Status] = "aflyst",
LOOKUPVALUE(
Dim_Stationer[Maalepunkt],
Dim_Stationer[TogNr], Fact_Events[TogNr],
Dim_Stationer[Stop navn], Fact_Events[Stop navn]
) = TRUE()
)
This formula matches the train number and station name from the fact table to the dimension table, filtering for rows where Maalepunkt is true. You can apply this approach to other statuses such as forsinket, planmaessig, and others.