Dynamic Pie Chart Based on Slicer Selection containing data source table name

[nikhil18]

I have created a Power BI dashboard with two data sources loaded as CSV files. Both data sources contain two common columns: "frauds" with values "yes" or "no" and "event id" with unique values. I have also created a pie chart in Power BI to visualize the distribution of frauds.

Now, I want to create a dynamic feature in my dashboard. Specifically, I'd like to have a dialog box with a dropdown menu containing table names. When a user selects a table name from this dropdown, I want the pie chart to update and display the respective fraud distribution for the selected table.

Could you please guide me on how to achieve this functionality in Power BI? If you have any helpful resources or video tutorials, please share them.

[parry2k]

@nikhil18 there is a lot more involved than what you posted on the original question. First I would not worry much about 1 million rows etc.

First thing first, since you are using Oracle I will do as much data preparation as possible in Oracle. Having said that in this case, I will just create a view in Oracle that combined the tables together and Power BI is just bringing the data in for the visualization.

Once the data is uploaded in Power BI then from there interaction and everything will be super smooth. In this case, the main component is the resources are the Oracle servers are managed and a lot of it will depend on it. I'm not an Oracle admin and I cannot suggest what needs to be done on the Oracle side so that the view performs to provide the data efficiently. 

All in all, it is going to be mix optimization, and Power BI will just need to bring the data in and visualize it. Again, it is not about million rows, and don't have that number take you off the track. I work with solutions where my customers have 500 million+ rows and fully optimized solution work very smoothly where volume is just a number.   

[nikhil18]

Thank you very much @parry2k  for your detailed response and guidance. I greatly appreciate your insights and suggestions.I will apply the suggested approach, and if I encounter any further questions or challenges along the way, I'll certainly reach out for guidance.

[parry2k]

@nikhil18 I'm a little lost with your reply. What is going to be the source when you are using the production environment? Is it still CSV files or any other source? Also, where these CSV files are stored? What is the size difference (records) between the Test and Production environments? How often Power BI dataset need a refresh, in other words, how often do these files get updated?

[nikhil18]

Thank you for your patience @parry2k  and for further clarifying the context. To make it more relatable, let me illustrate the issue with a simplified example:

In my testing environment, I used a static CSV snapshot containing one month's worth of data. However, in our actual production scenario, we will connect to an Oracle database, which will be refreshed every 30 minutes. This means that the data volume is significantly larger and more dynamic.

To give you a sense of the complexity, consider a real project where we could have not just two tables but potentially six or seven, each spanning six months' worth of transaction records. These tables could easily contain close to one million records each, with multiple columns.

Now, picture a Power BI dashboard featuring five to six interactive graphs, all in sync with each other. In this dashboard, I need to implement a filter that allows users to select a specific table name. When a table is selected, the corresponding data from that table should populate all the graphs on the page.

The challenge lies in efficiently handling this dynamic scenario where we're dealing with extensive, frequently updated data from multiple tables. I'm looking for an optimal solution to ensure our dashboard remains responsive and provides users with the data they need.

Your guidance and expertise in handling such complex setups would be greatly appreciated. Thank you for your understanding.

[parry2k · Accepted solution]

@nikhil18 what @Idrissshatila suggested would work, but if I have to do this, I will add another column in each table, let's call it "Type" and give the values "Type 1 " and "Type 2", and then append these tables. All this is easy to do in Power Query. Once done, you will have one table with data from both tables with a column called Type. Now you can easily setup a slicer on this new column which will filter the data based on the selection.

[nikhil18]

@parry2k Thanks for the reply. This will work but the two table i have are the bank trasactional data. For a month i have million of records in each table, if i append this table in real time it will not be an optimal solution plus it will also increase load. Is there any optimal way to do so? 

[parry2k]

Not sure why it is not going to be an optimal solution. What made you think so? 

Regarding time with the append step, have your tried to check how much time it added? Inwijld

recommend to test it and then provide the feedback. 

[nikhil18]

Thanks for your input, @parry2k.

In our testing environment, I haven't observed any performance issues so far. However, as I can't easily replicate our production setup for testing, I'm cautious about potential challenges.

I'm not sure about the backend process in Power Query for appending data, but in traditional SQL with a UNION operation, this could lead to problems in production. To ensure we proceed optimally, I'd greatly appreciate your confirmation that this approach should work in a production environment. Your expertise would be invaluable in making this decision.

Looking forward to your insights. If you believe it's a viable solution, I'll move ahead with it.

[Idrissshatila]

Hello @nikhil18 ,

you could group both columns from both table in field parameters and you could rename it as you want , then users could switch between these two columns.

check it out https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea 💡

[nikhil18]

@Idrissshatila  Thanks for the reply but i dont want to switch between 2 columns, i want to create a filter which could switch between 2 tables. Based on the selection of the table name the data from that table should be populated in the graph.