How to call SQL function in Power BI

[abbynie08]

I have a table valued function in sql, and I want to call the table valued function in power bi to get the final output.

The function looks like this, 

CREATE FUNCTION [dbo].[result]
(
@studentID int
,@class varchar(50)
)
RETURNS @ScORE TABLE
(score int)

AS
BEGIN

INSERT INTO @ScORE
SELECT score from xxx join xxx where xxx

RETURN
END

My desired output in power BI is I will have two slicers, studentID and class. When I select a student and the class, it will call the function and the PBI will have a table that shows the ouput, which is the score of that student. And if I choose another class, the PBI will only the score for that class. 

Can someone point me the right direction?

Thanks

[parry2k]

@abbynie08 you cannot pass slicer value to your underline query but you can use parameters in power query

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[abbynie08]

Thanks for the reply. But can you be more specific? How to pass the paratmeters? There are several tables I used in the SQL to get the final score.

Ideally, I can join these tables to get all the combinations for all the students in all classes. And then import the table in PBI and select student ID and class to get the score for that student in the class. But because the dataset is too large after joining, I did not populate the whole tables.

Instead, I write the function, which takes studentID and classID as input and then based on the input, generate the output score. 

I guess my question here is, how to pass the parameter in this case?

Thanks!

[v-deddai1-msft]

Hi @abbynie08 ,

>>I guess my question here is, how to pass the parameter in this case?

You can refer to : https://community.powerbi.com/t5/Desktop/How-do-I-pass-parameters-to-my-SQL-statement/td-p/118716

 and  https://reevessmith.wordpress.com/2014/08/19/power-query-and-stored-procedures-with-parameters/ to pass the parameter to stored procedures.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[parry2k]

@abbynie08 did you read the post I mentioned in my reply

[abbynie08]

I did look at the post and watched some tutorial videos. However, the parameters in the post and the videos are used when there is already an existing table in the PBI. And the parameters are used to filter the existing tables, which in my case, means that I will have the full table which consists of all the combinations of studentID and class, and then based on the parameters selected, it wil only show the score for that specific student in the specific class.

However, what I am trying to do is that I have multiple tables in the SQL, and I have to join some tables and get some calculations in order to get the the full table which consists of all the combinations of studentID and class. But because the full table is too big to be loaded by PBI, I decided to create a function that takes studentID and class as input in the function and the function will return the final score and I only want that to be showed in PBI. So the calculation is done in SQL instead of PBI. 

Please let me what you think. Thanks!