Can i call Stored Procedure with Direct Query?

Yes, solution exists. You can use stored procedure in SELECT from OPENROWSET.

SELECT * FROM OPENROWSET('SQLNCLI','server=<SQLservername>;trusted_connection=yes;','EXEC <dbname>.<schema>.<storedprocedure>')

Note: OPENROWSET construction is withnout spaces. This example is Windows authentication. Connection is under user security context. You need have RSWindowsNegotiate of AuthenticationTypes in configuration of ReportServer. Registred SPN of ReportServer portal, registred SPN of source SQL server (where is stored procedure) and set delegation in AD .. https://docs.microsoft.com/cs-cz/power-bi/report-server/configure-kerberos-powerbi-reports

Stored procedure script can not use tempdb, because OPENROWSET don’t support it. OK, solution exsists, but not easy and it isn’t quick win. Insteed of you can use table variable and so.

On source SQL server you mast enable Ad Hoc Distributed Queries. User Login must have read permissions of source database and execute permmission (execute your stored procedure).

If you need use SQL login, you mast store OPENROWSET SELECT with password:

SELECT * FROM OPENROWSET('SQLNCLI','server=<SQLservername>;Persist Security Info=True;UID=<SQLlogin>;PWD=<yourPassword>','EXEC <dbname>.<schema>.<storedprocedure>')

It isn’t safety.

BUT.. You can create encrypted view on source SQL server DB with OPENROWSET SELECT:

CREATE VIEW PBIView_encrypted With ENCRYPTION
AS

SELECT * FROM OPENROWSET('SQLNCLI','server=<SQLservername>;Persist Security Info=True;UID=<SQLlogin>;PWD=<yourPassword>','EXEC <dbname>.<schema>.<storedprocedure>')

Go

  And in your PBI report you can call SELECT from your encrypted view. Hope I help you.