Forum Discussion

ahmedshalabyy12's avatar
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)   ...
  • mh2587's avatar
    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
    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.