how to create this static chart into dynamic using ms SQL server(parameters used)

[Sid25]

i have created this chart using the excel sheet but it won't update the data in the real time so i wanna crerate the same using the ms sql server for that i am using stored procedure(ms sql server),i have created the parametrers and invoke it by passing the parameters using the invkoke function which pops up a box with parameters to be inserted manually.i don't wanna use it that way,i need to convert it into a chart that is clickable.when i click on one chart it renders or displays the another one ,like used in the static mode(static file).how to set the sp to get it done.any suggestion and even a hint would be great.

by clicking on year chart there should be monthly sp should be called in order to achieve the same but i don't know the way to get it done.

thanks in advance

[Mariusz]

Hi @Sid25 

You can not call the stored procedure by changing the visual, power bi has its own engine that is a lot faster than SQL server is.

If your dataset is too big like 100 of millions of rows then you can use Direct Query or Composite Model (Mixed import and Direct Query ), if the model isn't so big then just load all.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Sid25]

The problem is that data stored in the server is confidential so i can't migrate it to somewhere else and can't load the entire at once due to the the volume of the data that's why i wanna achieve the same with the help of stored procedure. i am kinda new to this ,i usually with r and python so correct me if i am wrong.

I want the selection from the bar chart to be passed as a parameter of stored procedure during runtime. SP should be executed dynamically

please guide me through to achieve the same.

thanks in advance

sid

[amitchandak]

@Sid25 , Try if these can help

https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill

https://www.youtube.com/watch?v=OMr9_EdYlcw

https://www.tutorialgateway.org/drill-up-and-drill-down-reports-in-power-bi/