Parametrized sql user defined function in PowerBI Desktop dashboard page

[csat]

Hi All,

Need guidance on utilizing parametrized sql user defined function in PowerBI Desktop. I have created couple of dashboard pages for 2 different countries. Wanted to show Top 5 Customers w.r.t each country on each page. Created parametrized function and mapping the same in one page. Its accepting the parameter and populating based on my inputs. For the other Country dashboard i want to map the same function by passing different country to the parameter. But its not populating as per my expectation.

Pls suggest how to do this.

Thanks

CS

[v-juanli-msft]

Hi @csat

How do you apply parameters for your dataset?

use parameter to connect to sql database or add parameter to filter data?

reference here introduce working with parameter in Power BI Desktop.

For you requirement, I would suggest you connect to sql database including all data you would like to use in your report, then put a slicer for each visual or report.

When the two visuals are in the same report, you could put the "country" column in two slicers, then edit interactions for the two visuals and two slicers to make one visual change with one slicer but not be affected by the other silcer.

Best regards

Maggie

[v-juanli-msft]

Hi @csat

I have created couple of dashboard pages for 2 different countries. Wanted to show Top 5 Customers w.r.t each country on each page.

You can use DAX function like Ranks to achieve it.

Please look at my test.

create measure in Sheet3

sales measure = SUM(Sheet3[sales])

rank = RANKX(ALLEXCEPT(Sheet3,Sheet3[country]),[sales measure],,DESC)

Then Enter data to create a new table with a column

Then create measures

selected = MIN([top])

if show = IF([rank]<=[selected],1)

Then add the measure [if show] in the Visual Filter

Reference how to create TopN in power bi

dynamic-topn-ranking-in-power-bi

Created parametrized function and mapping the same in one page. Its accepting the parameter and populating based on my inputs.

You can use the measure on any dashboards.

What do you mean by "Its accepting the parameter and populating based on my inputs.", could you give me an example?

For the other Country dashboard i want to map the same function by passing different country to the parameter. But its not populating as per my expectation.

Could you show me what you have done and what is your expected result?

Best Regards

Maggie

[csat]

Hi,

Thanks for your reply. Here my intention is not rankingl let me give more clarity on my requirements.

I have created sql user defined function with @category as input parameter , wanted to use the same as dataset for both charts.

Fetching filtered data from sql table based on parameter.  I dont want to create multiple dataset in PowerBI. Hope this clarifies.

[csat]

Any Suggestions from Experts?