Jim has attended:
Power BI DAX course
Using a DAX query in Power BI report view
I created a DAX query that restricts data reported to specific users by using a filter. However I can't find how to report the filtered data using the Report view so that I can publish the report.
RE: Using a DAX query in Power BI report view
Hi Jim,
Thank you for the forum question.
In Power BI, when you create a DAX query (typically using DAX Studio or the Query view in Power BI Desktop), it's important to understand that DAX queries themselves are not directly visualizable in the Report view. Instead, you need to translate the logic of your DAX query into measures, calculated columns, or filters that can be used in visuals.
Here’s how you can achieve your goal of filtering data for specific users and showing it in the Report view:
Step-by-Step: Filter Data for Specific Users in Report View
1. Create a Measure or Calculated Table
If your DAX query filters data for specific users, you can convert that logic into a measure or a calculated table.
For example, if your DAX query looks like this:
EVALUATE
FILTER(
Sales,
Sales[UserEmail] = "jens.bonde@example.com"
)
You can instead create a calculated table:
FilteredSales =
FILTER(
Sales,
Sales[UserEmail] = "jens.bonde@example.com"
)
Or a measure:
Filtered Sales Amount =
CALCULATE(
SUM(Sales[Amount]),
Sales[UserEmail] = "jens.bonde@example.com"
)
2. Use Row-Level Security (RLS) for Dynamic Filtering
If you want the report to show different data for different users, use Row-Level Security:
• Go to Modeling > Manage Roles
• Create a role like UserFilter with a DAX expression:
• Sales[UserEmail] = USERPRINCIPALNAME()
• Assign this role when publishing to Power BI Service.
This way, each user sees only their data.
3. Build Visuals in Report View
Now that your data is filtered via a measure or RLS:
• Go to the Report view
• Use visuals (tables, charts, etc.) and drag your filtered measure or calculated table fields into them
• Format and design your report as needed
4. Publish the Report
Once your visuals are ready:
• Click File > Publish > Power BI Service
• Assign roles in the Power BI Service under Security for the dataset
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Using a DAX query in Power BI report view
Thanks, RLS is what I'm trying to use but I'm not able to translate the DAX query I created in the PBI editor where I tested it. It uses an additional table, user_access, to define the security which limits the output to only be the rows to which the user has access:
CALCULATETABLE(
emp_table,
FILTER(user_access, user_access[user_principal_name] = USERPRINCIPALNAME]()))
The relationship between tables user_access and emp_table is defined in the Model view using the name of the company. This gives me the results I expect based on the user_access table.
I'm trying to use the same logic in RLS which I'm sure is be possible but I haven't managed to get it to work.
RE: Using a DAX query in Power BI report view
Hi Jim,
You're absolutely right that this logic should be possible to implement using Row-Level Security (RLS) in Power BI. The key is translating your DAX logic into a format that works within the RLS role definition.
Here’s how you can approach it:
Your Goal
Limit access to rows in emp_table based on the user_access table, using the relationship on company name and filtering by USERPRINCIPALNAME().
Steps to Implement RLS with a Related Table
1. Ensure Relationships Are Set Correctly
o user_access[company_name] should be related to emp_table[company_name].
o The relationship should be active and single-directional from user_access to emp_table.
2. Define RLS on the user_access Table
o Go to Model view → Manage Roles.
o Create a new role (e.g., UserAccessRole).
o Select the user_access table and enter the following DAX filter:
o [user_principal_name] = USERPRINCIPALNAME()
o This filter ensures that only rows in user_access matching the current user are visible.
3. Leverage Relationships
o Because of the relationship between user_access and emp_table, filtering user_access will automatically filter emp_table.
Why This Works
Power BI applies RLS filters at the table level. By filtering user_access to only include rows for the current user, and having a relationship to emp_table, the security context flows through to emp_table.
Testing It
• Use View As Roles in Power BI Desktop to simulate different users.
• Make sure the expected rows in emp_table are visible only when the corresponding user_principal_name is active.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
