How to build an SQL based report, with slicers, and show tables based on slicer selection

[gdijim]

Hi everyone, 

I'm brand new to PowerBI, and I may be thinking about this problem incorrectly, so if there's a better way to think about this, please let me know.

I have an MySQL server DB that I can query from PowerBI.  I need to provide a list of companies that I need to represent as slicer radio buttons to select the company.  I've figured out how to query the DB, and pull a list of companies, but I haven't been able to figure out how to apply the selected company as a parameter for the rest of the SQL queries that I need to present the data in other tables in the report.

I need all the tables in my report to show their queries based on that provided company parameter.

I've tried a variety of ways to do this, using measures, but it doesn't look like measures can apply to an SQL query as a parameter.

I'm unsure if this is a matter of needing to structure my database differently, or if I'm just not thinking correctly about how to use PowerBI.

Any help is appreciated.

Thanks!

-Jim Bentley

[marcelsmaglhaes]

Hey @gdijim ,

In Power BI, when dealing with filters, it's often more efficient to create dimensions table rather than trying to filter by a parameter directly in your SQL queries. This approach simplifies your data modeling and provides a more robust solution for your reporting needs.

Here's a step-by-step guide to help you implement this:

1. Create a Company Dimension Table:

In Power Query, build a new table that contains a unique list of all the companies in your data. This will be your "Company" dimension table.

2. Define Company Attributes:

Include columns in your Company table for the company name and any additional attributes or information you may need for analysis.

3. Establish Relationships:

In the Power BI model view, establish relationships between your Company table and the corresponding columns in your fact tables (e.g., Sales, Orders) based on the 'Company' column. Ensure you set the appropriate cardinality and cross-filter direction.

4. Create a Slicer:

Develop a slicer visual in your report and connect it to the 'Company' field in your Company dimension table.

5. Filtering by Selection:

When you select a company from the slicer, it will automatically filter the data in your fact tables, making it a central filter for all your data.

This approach streamlines your data model, simplifies filtering, and enhances report performance. It's especially beneficial when dealing with extensive datasets and complex data relationships.

By implementing a Company dimension table, you're creating a more structured and effective solution for your Power BI reporting.

If this post helps, please mark as solved. 

Best regards,
Marcel