Dynamic SQL View based on button click

[SUGISUGAN]

Hi, I am new to power bi. My requirement is to have 3 button in Home Page, General Raw data view in General Page.                     On click of Button1 - General Page report must bind data with View1  like select * from View1

On click of Button2 - General page report must bind data with View2 (select * from View2)

On click of button3 - render data from View3(select * from view3)

all columns are same in 3 views and i have to dynamically pass this on button click.

Kindly help how to achieve this in desktop and the functionality must work in service too.

power bi version using: Version: 2.136.1202.0 64-bit (September 2024)

[SUGISUGAN]

Hi Sam,

I would request a help in advance to it.. to pass the view name dynamically based on button click using parameterization. As my views will be deployed one after the other, i cannot proceed with append option.. 

A dynamic SQL query takes view name and a change in the layout will be adding new button and pass the new view to the existing parameterized code. WIl this work on desktop and service?

[SamWiseOwl]

Hi @SUGISUGAN 

Have you considered using the Append feature?

Connect to each view the Query Editor.

Add a custom column called Number and for the view_1 have 1 for 2 etc

Then Append all 3 views together.

Create 3 bookmarks that save the Single query filtered to the 3 different values.

Add a bookmark navigator to switch between them or have 3 seperate buttons that go to a bookmark,

[SUGISUGAN]

HI SAM,

I got the result as expected and made tweaks accessing the bookmark from a button click.

Thanks a lot for your valuable time.

I have a question on performance. as we are appending, and all the views are executed at once.

Volume of data approximately is View 1 : 10lac (per year) , View 2 (75000 per year).. and i am rendering the data for 3 years.. will we get any performance issue? 

[SamWiseOwl · Accepted solution]

Hi @SUGISUGAN 

Great to hear, if it worked please mark as a solution.

Best way to find out is giving it a try. Direct Queries by their very nature are slower than imported data.

The alternative is having 3 bookmarks for the same page with different visuals for the different views or 3 seperate pages for the 3 views.

Both of those require a more complicated report but in theory may run faster.

Make sure to go into Settings -> Current Report -> Query Reduction and make use of the options there. These will help with the direct query running smoother.

[SUGISUGAN]

Hi Sam,

Thanks for your reply, let me try this option and confirm you at the earliest..