Measurements with filter from a different tables

[SteenSoernesen]

I'm trying to make a conutrow where I need infomations from a different tables to get the correkt count numbers for different aspects like aflyst(cancelled), forsinket(delayed), On Time, Planlagt aflysning(planned cancellation) and produktion(production)

Rapportering Fact_Events have most of the info needed to do the countrow measurement, except whether a station is a measuring point or not.   

That measuring point is found in the table rapportering Dim_Stationer where the name Maalepunkt(measuring point) is a true/false setting and can tell me how many Maalepunkt(measuring point) each TogNr(TrainNo) have on their route.

[v-sshirivolu]

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 

[SteenSoernesen]

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.

[v-sshirivolu · Accepted solution]

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.

[Ritaf1983]

Hi @SteenSoernesen 

It sounds like you're trying to perform a measurement that involves information from multiple tables – a scenario where the data model structure is critical.

From what you’ve described, it’s possible that your data model isn’t following a proper star schema – meaning a fact table (like Fact_Events) that connects via clear relationships to multiple dimension tables (like Dim_Stationer).

When there’s no direct relationship between the tables, Power BI may not propagate filters as expected.
So it’s important to make sure there’s a relationship (e.g., via Station ID) between Fact_Events and Dim_Stationer, and that the model allows for proper filter flow.

More information abput star scheema here :
https://www.phdata.io/blog/data-modeling-fundamentals-in-power-bi/
To provide more targeted help, I recommend sharing your PBIX file (or a simplified version) via a public cloud service like Google Drive, Dropbox, OneDrive, etc.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly