The connection between Power BI and AX 7 is been established through OData feed (By using the AX 7 URL).
Consider the scenario where User – 1 is in Company – 1 and User – 2 in Company – 2 (Which is being specified in System Administration module). Depending on the company assigned to users the data must be populated. These users are added to a new Power BI workspace group in PowerBI.com.
The Power BI report is being generated and published to Power BI through Power BI desktop with User – 1’s credentials (Logged in with user1.organization.com). So, only the User – 1’s company data will be displayed for the rest of the users, in our case for User - 2.
Suppose if we change the company of User – 1 to Company – 3, then Company 3’s data is getting displayed in the Power BI report as expected. The generated report is being published to the newly created Power BI group and the same is reflected to the rest of users, in our case for User – 2.
Irrespective of company mapped to the User – 2, the data is populating based on the company mapped to User – 1, as the data is published through the User – 1 login in Power BI Desktop.
We need the Power BI dataset to be populated based on the company mapped to the user in Office 365 for Operations - System Administration module.
We have tried refreshing of dataset in power BI, yet couldn’t able to achieve any solution.
Could you please help me on this issue to be resolved.
Hey Elamathi,
You might take a look at the row-level security option of Power BI. Basically, you would create the report using the credentials of someone who has access to all the companies, but then restrict the results that people could see when they access the report on the Power BI service.
https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/
Take a look at the link above and see if that fits your needs.
Hi Jake,
By using this RLS in Power BI report, we can able to restrict the data access based on some static conditions like,
[Company] = “XXX”
But my thing is, the value within the double quotes will also gets changed dynamically.
The same report should be viewed by people in other company say “YYY” with corresponding data. In this case, the condition [Company] = “XXX”, will retrieve empty records and it also will not return company YYY’s data.
Thanks,
Elamathi. S
Hey Elamathi,
I’m not sure, but I’d think the way you’d do this would be in two pieces.
First, you’d created multiple roles. There would be a role for company XXX and a role for company YYY. Users belonging to role XXX would only see data from company XXX when they login. The role would use static DAX like you’ve listed - [Company] = “XXX.”
Next, you’d assign user’s to the roles. Roles that you wanted to see company XXX would belong to the “XXX” role and users you’d want to see data from company YYY would belong to the “YYY” role. In this sense, you’re basically hard-coding in the roles and access to the companies.
Does that work?
Hi Jake,
I have created two roles against the Dataset, one with company XXX and added User 1 as well as User 2 to that Role 1(Both the user’s company are configurable and they can be configured with two companies). And both the users also assigned to other one, Role 2 with company YYY.
By setting company against User 1 as XXX and User 2 as YYY,
When I refreshed and opened the report, it is showing data for the company XXX against both the users. In my case, The data not displayed for the User 2 in company YYY, instead its showing company XXX’s data.
According to my understanding, the dataset takes data from the user’s company who publishes the report into Power BI. irrespective of the company role setup in Power BI.
When the user is in company XXX, it should display XXX’s data and when he changed to YYY, it should display YYY’s data accordingly.
By following the solution mentioned above, it is displaying empty data or the single company data (According to the published user’s company)
Is there any other way to take the data dynamically in Power BI?
Thanks,
Elamathi. S