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.
Hi SteenSoernesen ,
Thanks for reaching out to the Microsoft fabric community forum.
I followed these steps:
Imported Data
Loaded both Fact_Events and Dim_Stationer tables.
Fact_Events includes columns like EventID, Status, StationID, etc.
Dim_Stationer includes StationID, Maalepunkt (True/False), and StationName.
Created Relationship
In Model view, set up a single-direction relationship: Dim_Stationer[StationID] → Fact_Events[StationID].
Tried Using RELATED() First
The following formula gave an error:
CALCULATE(
COUNTROWS(Fact_Events),
RELATED(Dim_Stationer[Maalepunkt]) = TRUE()
)
I switched to using TREATAS() along with direct filtering on the dimension table.
Canceled_Events :=
CALCULATE(
COUNTROWS(Fact_Events),
Fact_Events[Status] = "aflyst",
TREATAS(
VALUES(Dim_Stationer[StationID]),
Fact_Events[StationID]
),
Dim_Stationer[Maalepunkt] = TRUE()
)
I applied similar logic for Delayed_Events (forsinket), OnTime_Events (planmæssig), and Planned_Cancel_Events (planlagt aflysning). Now, each measure correctly includes only events from stations where Maalepunkt is TRUE.
Visual Setup
Bar chart: Compared the different event type measures.
Slicer: Added for StationID or StationName from Dim_Stationer.
Card visuals: Displayed KPIs like canceled, delayed, etc.
All visuals now automatically filter for Maalepunkt = TRUE().
This approach is effective for filtering a fact table based on a Boolean field in a related dimension table. TREATAS() is useful for simulating the relationship in CALCULATE() when RELATED() isn't applicable due to row context limitations.
Find attached document for your reference
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it
Best Regards,
Sreeteja.
Community Support Team
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.