Forum Discussion
Post PatronSimultaneously filtering on separate Tables?
Good Morning
Can one use Power BI filter on Three separate tables simultaneously ? That is, if in a report I enter a Client Number, is it possible to bring-in all the necessary details for the Client (residing in the separate tables). What would be the superior way to structure any SQL Query for this ?
Regards
Hi F_Reh
Yes this is exactly what Power BI relational model is designed for. As long as your three tables share a common key like Client Number and are connected via relationship in Model view then filtering on Client Number in a slicer or filter will propagate across all related tables
Structure to use - you can set up star schema with one dimension table have unique Client Number records with one-to-many relationship connected to fact table.
On the SQL side - Instead of one large joined query you can import each table through SQL views like SELECT * FROM Clients, SELECT * FROM Orders, SELECT * FROM Transactions if you need all the columns for reporting then build the relationships in Power BI's Model view using Client Number as the key
2 Replies
- krishnakanth240
Super User
Hi F_Reh
Yes this is exactly what Power BI relational model is designed for. As long as your three tables share a common key like Client Number and are connected via relationship in Model view then filtering on Client Number in a slicer or filter will propagate across all related tables
Structure to use - you can set up star schema with one dimension table have unique Client Number records with one-to-many relationship connected to fact table.
On the SQL side - Instead of one large joined query you can import each table through SQL views like SELECT * FROM Clients, SELECT * FROM Orders, SELECT * FROM Transactions if you need all the columns for reporting then build the relationships in Power BI's Model view using Client Number as the key
- rajendraongole1
Hi F_Reh -If the three tables are genuinely related and you need one combined result set, use a proper JOIN, for example:
SELECT
c.ClientNumber,
c.ClientName,
s.SalesAmount,
sv.ServiceDate,
ct.ContractNumber
FROM DimClient c
LEFT JOIN Sales s
ON c.ClientNumber = s.ClientNumber
LEFT JOIN Service sv
ON c.ClientNumber = sv.ClientNumber
LEFT JOIN Contracts ct
ON c.ClientNumber = ct.ClientNumber;
However, be careful with this approach. If each table has multiple rows per client, joining them together can create a many-to-many multiplication of rows. For example, 5 Sales × 4 Service records × 3 Contracts could produce 60 rows for a single client.
SQL → separate tables at their appropriate grain → Power BI relationships → Client dimension/filter
If the three tables are actually unrelated and you only want to retrieve matching client details, then the SQL design would depend on their respective grain and relationships.
