Forum Discussion

ahmedshalabyy12's avatar
ahmedshalabyy12
Helper V
9 months ago
Solved

Problem in The Relationships

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 

 

 

Need Help
  • mh2587's avatar
    mh2587
    9 months ago

    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's avatar
    parry2k
    9 months ago

    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.

2 Replies

  • mh2587's avatar
    mh2587
    Super User
    9 months ago

    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's avatar
    parry2k
    Super User
    9 months ago

    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.