Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jan_Trummel
Helper III
Helper III

See total sales despite rolle filter

Hello to the forum,

 

I have the sales of people A, B, C, D and E. Here is the Sales table:


Tables "Sales"Tables "Sales"


I want to make sure each person only sees their data. For example, I create the following role for person A:

[person] == "A"

 

The challenge now is that I also want to display the total sales in the report. However, because the data is filtered by the role, only that person's data is visible (in the example, only Person A's $100). One question is therefore how I can still access the total sales using a measure.

 

So my goal looks like this (with role filter active for person A):

This would be my goal for Person AThis would be my goal for Person A


I've already tried it with the following measures:


Total sales = CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Sales[Person]))

Total sales = CALCULATE(SUM(Sales[Amount]), ALL(Sales[Person]))

However, in both cases, Total sales only reports Person A's sales.

Is it even possible to “break through” the role filter with DAX?

 

The only other option I can think of would be to duplicate the table. However, this could quickly lead to performance problems with large amounts of data and would also make the data model very confusing. Therefore, it would be great if I could calculate the total sales using a measure from the original table.

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your semantic model looks like, but in my opinion, if RLS is implemented, the total might not be shown as you want. I suggest having one more fact table, not dupulicating only, but also making it aggregated. And then if the row on the report is filtered by sales person, show sales person's amount, otherwise show agg table's amount.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your semantic model looks like, but in my opinion, if RLS is implemented, the total might not be shown as you want. I suggest having one more fact table, not dupulicating only, but also making it aggregated. And then if the row on the report is filtered by sales person, show sales person's amount, otherwise show agg table's amount.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.