CODEWITHSUNDEEP
powerbipowerquery
Lance Charger
Lance Charger

Reputation: 73

PowerBI Filter on multiple fields across multiple data sources

I have work in Tableau for years, but my new employer uses PowerBI and I can not wrap my head around the one relationship only thing.

I have a 2 fields from 5 different datasources I need to be able to filter by. I have a "main" datasource with the bulk of my datapoints that I have merged in PowerQuery with the other four datasources using a Date field as the 1 relation and a Text field with only two options as the 2 relation. The Text field contains the names of our company's two business units.

I have set up a PBI report that filters fine on Date, but I need the report to filter on both Date and Business Unit and have the datapoints from all five datasources filter to data only for records that contain the selected Date and Business Unit. I can not add a Date filter and then add five identical Business Unit filters and present this to my C-suite.

I understand how calculate() works (SQL where). I understand how filter() works (LOD calc, sorta). I am using them in some of my measures in the table pictured below.

If this were Tableau, I would probably join on the Date field and blend on the Business Unit field, or blend both fields, or create Parameters and filter on T/F calculated fields. I tried to figure out the PBI Parameter, but all it looks like is a series of numbers from 1 to 20? WTF am I supposed to do with that?

Sample Table

Upvotes: 0

Views: 3601

Answers (1)

TheRizza
TheRizza

Reputation: 2052

In Power BI, you need to create a dimensional model. You will have an Organization dimension (table) that has the Business Unit field, and a Date dimension that has dates. Then the keys from the dimension tables are related to the Fact (data) tables. More than likely, if the columns are the same between your five datasources, they might be best combined into a single fact table.

You model should look like this, with you fact table in the middle, and dimension tables in a "star" around them. They call this a "star schema."

Star Schema

A good model is essential in a Power BI report. Here is a good reference to get started with dimensional modeling in Power BI: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dimensional-model-report

Upvotes: 1

Related Questions