Problem in The Relationships

[ahmedshalabyy12]

Dears , 

I have 3 dimensions and one fact as following :

1- Dim_Date One to Many ( Fact_Email)

2- Dim_Site_code One to many (Fact_Email)

3- Dim_Operational_unit_Id  One to Many (Fact_Email)

supposed when i use site as a slicer to filter the number of the Operational Units  in this site but it doesn't work 

there's no filters on the page or the visuals applied 
Count of Operational Unit no slicers 

Count Operational Unit with slicers 

Both site code and operational unit in the slicers iam using them from the dimensions 

so why i the number of the operational units related to this site not filtered ? 

Thank you 

[mh2587 · Accepted solution]

The reason is that Power BI’s relationships only filter one way (from dimension -> fact).
So when you filter Site, it affects the fact table, but that filter doesn’t flow back to the Operational Unit dimension.
Both Site and Operational Unit are dimensions , so the filter doesn’t move between them by default.

Count of Operational Units = //Try this and put in the card
CALCULATE(
    DISTINCTCOUNT('Dim_OperationalUnit'[OperationalUnit_id]),
    TREATAS(
        VALUES('Dim_Site_Code'[Site_Code]),
        'Fact_Email'[Site_Code]
    )
)

[parry2k · Accepted solution]

@ahmedshalabyy12 @mh2587 provided great solution but here are the alternate approaches:

Count of Operational Units = //Try this and put in the card
DISTINCTCOUNT('Fact Table'[OperationalUnit_id])


//2nd approach:
CALCULATE ( 
     COUNTROWS ( 'Dim Operational Unit' ),
     CROSSFILTER ( FactTable[OperationalUnitId], 'Dim Operational'[OperationalUnitId], BOTH )
)

Basically, in the 2nd measure (above), you are creating a fact table to filter the operational dimension table.

[mh2587 · Accepted solution]

The reason is that Power BI’s relationships only filter one way (from dimension -> fact).
So when you filter Site, it affects the fact table, but that filter doesn’t flow back to the Operational Unit dimension.
Both Site and Operational Unit are dimensions , so the filter doesn’t move between them by default.

Count of Operational Units = //Try this and put in the card
CALCULATE(
    DISTINCTCOUNT('Dim_OperationalUnit'[OperationalUnit_id]),
    TREATAS(
        VALUES('Dim_Site_Code'[Site_Code]),
        'Fact_Email'[Site_Code]
    )
)