Dynamic filtering across multiple fact tables

Hi tingh 

o achieve dynamic filtering across multiple fact tables based on user selection in Power BI, you can use a combination of DAX measures and calculated columns. Here’s a step-by-step approach to help you set this up:

Step-by-Step Approach

1. Create a Mapping Table:

   • Create a mapping table that includes all projects with their respective flags for budget, actual cost, and forecast.

ProjectMapping = 
SUMMARIZE(
    UNION(
        SELECTCOLUMNS(BudgetFact, "ProjectID", BudgetFact[ProjectID], "HasBudget", 1, "HasActualCost", 0, "HasForecast", 0),
        SELECTCOLUMNS(ActualCostFact, "ProjectID", ActualCostFact[ProjectID], "HasBudget", 0, "HasActualCost", 1, "HasForecast", 0),
        SELECTCOLUMNS(ForecastFact, "ProjectID", ForecastFact[ProjectID], "HasBudget", 0, "HasActualCost", 0, "HasForecast", 1)
    ),
    [ProjectID],
    "HasBudget", MAX([HasBudget]),
    "HasActualCost", MAX([HasActualCost]),
    "HasForecast", MAX([HasForecast])
)
​

• Create Relationships:

  • Create relationships between the ProjectMapping table and the ProjectDim table using the ProjectID column.

• Create Measures for Filtering:

  • Create DAX measures to filter projects based on user selection.

ProjectsWithBudget = 
CALCULATE(
    COUNTROWS(ProjectDim),
    FILTER(
        ProjectDim,
        RELATED(ProjectMapping[HasBudget]) = 1
    )
)

ProjectsWithForecast = 
CALCULATE(
    COUNTROWS(ProjectDim),
    FILTER(
        ProjectDim,
        RELATED(ProjectMapping[HasForecast]) = 1
    )
)
​

• Create a Measure for Dynamic Filtering:

  • Create a measure that dynamically filters projects based on the selected financial year.

DynamicProjectFilter = 
CALCULATE(
    COUNTROWS(ProjectDim),
    FILTER(
        ProjectDim,
        RELATED(TimeDim[FY]) IN VALUES(TimeDim[FY])
    )
)
​

Example Visualization

• Slicer: Add a slicer for the financial year from the TimeDim table.
• Table/Matrix: Use the DynamicProjectFilter measure to show projects dynamically based on the selected financial year.
• Other Visuals: Apply the same measure to other visuals like donut charts, cards, etc., to ensure they reflect the filtered data.

Additional Tips

• Use Calculation Groups: If you have multiple metrics (e.g., budget, actual cost, forecast), consider using calculation groups to simplify your DAX measures and make your model more manageable.
• Optimize Relationships: Ensure your relationships are set up correctly to avoid performance issues and ensure accurate filtering.

By following these steps, you should be able to achieve dynamic filtering across multiple fact tables based on user selection in Power BI.