Forum Discussion

tingh's avatar
tingh
Frequent Visitor
1 year ago

Dynamic filtering across multiple fact tables

Hi, 

 

I'm looking for some ideas to achieve dynamic filtering across multiple fact tables based on user selection.

 

In my model, there are

  • 2 dimensions - Project and Time
  • 3 fact tables - Budget, Actual Cost and Forecast.

Please see relationships below 

 

Project Dimension stores a full list of projects while a subset of projects are in the fact tables given that only projects that have budget, actual cost or/and forecast will show up in the fact - e.g. 1000 projects in Project Dimension and 300 projects in Budget Fact, 200 projects in Forecast Fact, etc. 

 

We are looking at answering business questions based on the financial years user selected in the reports such as (the FY given in the below examples should come from the slicer and users can select more than one financial year): 

  • "Which projects have budget in FY 2025 and what are the forecast & budget for these projects?"
  • "How many proejcts have forecast in FY 2026 and can we show the projects and their details at a total level in 1 row (so sum up all the forecast, budget or actual cost) ?"

 

Here are examples of the expected output in red using sample data:

 

  • Show a list of projects with budget in FY 2025 - project AAA and BBB

  • Show a list of projects with forecast in FY 2025 - project AAA, BBB and CCC

  • Show a list of projects with budget in FY 2026 - project BBB

 

Key requirements are:

  • The list needs to be dynamic based on user input
  • The filtering can also work on other types of visual such as donut chart, card, etc - ideally can be used in the page filter

Ideally we'd like to drive the reporting from Project Dimension as it has a relationships with all the fact tables. I'm not sure if it's possible. So far I've thought about using DAX to calculate different flags (e.g. has budget, has forecast) as measures so we can filter the list but can't use them as page filter. Others have suggested that maybe we could have a mapping table that filters the Project Dimension to only keep the projects with budget/forecast/actual cost. 

 

What might be the best solution? Thank you.

Need Help

4 Replies

  • some_bih's avatar
    some_bih
    Community Champion
    1 year ago

    Hi tingh as budget, forecast and actual is complex task eigther which toool user use, please check some SQLBI links for this topic. I hope you will find your best scenario. 

  • suparnababu8's avatar
    suparnababu8
    Super User
    1 year ago

    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.

  • dk_dk's avatar
    dk_dk
    Super User
    1 year ago

    Would you be able to combine the 3 facts into one, and have Budget, Fact and Forecast be distinct columns in that one fact table? I think that would make the filtering easier, because you could just sent conditions that if Fact[Budget] = (BLANK) then the given project does not have a budget...

    • tingh's avatar
      tingh
      Frequent Visitor
      1 year ago

      Thanks for the suggestion. Yeah that could work, I've tested it on a small scale and just need to see how it goes with more data and other tables.