Hello Everyone,
I am not looking at Row Level Security. We already have the solution for the same. We don't want to rewrite the OLD Database structure and migrate it into Power BI ASAP. (Of course, the alternative is to use the row-level with a mapping table. Rewrite everything within the database 😞 )
Production Database Oracle.
Query Mode: Direct Query.
Scenario: We use the Table function with user information as a parameter to apply filter within the database. As we shared the sample code, we passed multiple arguments when calling the table's function. One of the parameters is UserName (or user-id); other parameters are prompt-driven.
In the example, we are passing UserName, Year, and Month.
The "UserName" parameter must set using the UserName function (Yes DAX, but we need to access it in M Query). Apart from this, we need to set @ParaMeter_Year and @ParaMeter_Month using slicers.
Following is the example code is written in SQL Server to share the requirement, but the solution needs to be compatible with the Oracle database.
USE ExampleDatabase
GO
CREATE FUNCTION [dbo].[udfTestFunction]
(
@UserInfo NVARCHAR(100)
,@Year BIGINT
,@Month BIGINT
)
RETURNS TABLE
AS
RETURN
(
SELECT
*
FROM ExampleDatabase.dbo.DBTable
WHERE YEAR(RecordDate) = @Year
AND MONTH(RecordDate) = @Month
AND Country = CASE @UserInfo WHEN 'user1@example.com' THEN 'India' WHEN 'user2@example.com' THEN 'US' ElSE 'Canada' END
/* In Actual we have user mapping table need to handle within Table Function */
)
/* Database Execution Example */
SELECT * FROM ExampleDatabase.dbo.udfTestFunction( 'user1@example.com',2021,2 )
/* Power BI with Direct Query - M */
SELECT * FROM ExampleDatabase.dbo.udfTestFunction( UserName(),@ParaMeter_Year,@ParaMeter_Month )
/*
Values for @ParaMeter_Year and @ParaMeter_Month must be set via
Slicers.
*/
Thanks for your time in reading this. And I am sorry for sharing a duplicate question because the answer share within the following post may be still valid:
UserName and Power Query
https://community.powerbi.com/t5/Power-Query/Can-we-retrieve-logged-in-user-name-in-Power-BI-Query-e...
Slicer and Parameter
https://community.powerbi.com/t5/Desktop/Slicer-Value-as-a-parameter-to-stored-procedure/m-p/220397
Dynamic M query parameters in Power BI Desktop (preview)
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
It is copy-paste from Microsoft Documentation.
Considerations and limitations
There are some considerations and limitations to consider when using dynamic M query parameters:
- The feature is only supported for M based data sources. The following DirectQuery sources are not supported:
- Other unsupported data sources: Oracle (Why? Why?), Teradata, and Relational SAP Hana, PostgreSQL
