PUG Exchange

1.  X-axis based on logged in user

Posted 7 days ago
Hi everyone,

I've had a request from a client to dynamically change the X-axis labels of a graph, based on who is logged on. The labels will be "Company 1" "Company 2" etc, and when the company matches the company of the user who is logged in, it'll display "My Company".

I don't think this is possible, because the x-axis comes from a column, and columns aren't dynamic like measures.



------------------------------
Ben Oastler
Practise Director - North America
+61411681138
------------------------------


2.  RE: X-axis based on logged in user

Posted 7 days ago
Hi Ben,

If you could incorporate row level security (RLS) into your data set this may allow you to achieve what you are after as you could allow only data points that are viewable by the user logging in to be displayed.

Cheers,

------------------------------
Andrew Exley
BI and Data Analytics Consultant
Adelaide, Australia
------------------------------



3.  RE: X-axis based on logged in user

Bronze Contributor
Posted 6 days ago
​Yes, as Andrew indicates the way to fully automate this is using RLS.

However, you could consider using a bookmarked view for each user with pre-filtering applied, and then publish just the bookmarked version to the respective individual? More work arguably, but a possibility if RLS is not possible for you.

------------------------------
Simon Hill
Manager
TTX Company
Solihull
------------------------------



4.  RE: X-axis based on logged in user

Posted 5 days ago
  |   view attached
Could you create a calculated column that returned "My Company" if the right condition was met? I'm probably oversimplifying here, but attached is an example workbook.

The calculated column formula would look like this.
PersonalizedCompany = IF('Company V'[Company]="Company B", "My Company", 'Company V'[Company])

However, I did notice that you can't directly use the "USERNAME" function in a calculated column. If this doesn't work, we could probably generate a dynamic table to populate the graph.

Could you attach an example?

thumbnail image


------------------------------
Brent Lightsey
Analytics Consultant
FirstLight Analytics
Oklahoma, USA
------------------------------

Attachment(s)



5.  RE: X-axis based on logged in user

Posted 5 days ago
Hi Brent,

Yeah that's exactly the problem. A calculated column is materialized, so it's calculated once when the tabular model is processed. Think of Username() as a measure, it can't be used in a calculated column. And can't use a measure on a X-axis of a graph, it must be a column, which makes sense.

cheers
Ben

------------------------------
Ben Oastler
Practise Director - North America
+61411681138
------------------------------



6.  RE: X-axis based on logged in user

Posted 5 days ago
Hey Simon,

Bookmarks might have to be the way to go. But aren't all bookmarks exposed to all users? For security reasons, the user's can't see other company's reports. I might have to maintain a separate PBIX file for each company. Like you said, a bit more work.. hrmmm

thanks
Ben

------------------------------
Ben Oastler
Practise Director - North America
+61411681138
------------------------------



7.  RE: X-axis based on logged in user

Posted 5 days ago
Hey Andrew,

I have RLS security working. It's just a matter of changing the label of the X-axis. I don't think it's possible, as columns are calculated when the tabular model is processed, not when the user logs in.

cheers
Ben

------------------------------
Ben Oastler
Practise Director - North America
+61411681138
------------------------------



8.  RE: X-axis based on logged in user

Posted 2 days ago
Separate PBIX files are sounding more and more necessary. Since the column is processed at query time the system needs to know the currently logged in user at that time as well.

I've been trying to think of another way to do that dynamically, but just can't think of it since USERNAME() is only available in measures (logically, because how would a scheduled refresh data set know the user except the system user), then you can't figure it out at query process time. And even if you could make a measure based on USERNAME, you can't drag a measure over to the axis area of a chart. You can drag it to the tooltips if it returns a string.

I wonder if this feature is worth it enough to the client to warrant splitting the PBIX files?

I'm really curious to hear how you resolve this one! Wish I could be more help.

------------------------------
Brent Lightsey
Analytics Consultant
FirstLight Analytics
Oklahoma, USA
------------------------------



9.  RE: X-axis based on logged in user

Posted 2 days ago
​Hi All,

I have done something similar to create a dynamic legend for a chart, but I don't see any reason why this couldn't work using RLS.

The basics concept is to create a new table in DAX, which is a union of your data for each company, something like the below.

Index Company User Value
1 My Company User 1 2000
2 Company 2 User 1 1000
3 Company 3 User 1 3000
4 Company 4 User 1 12000
5 Company 5 User 1 13000
1 Company 1 User 2 2000
2 My Company User 2 1000
3 Company 3 User 2 3000
4 Company 4 User 2 12000
5 Company 5 User 2 13000

You can then use RLS to filter the User Column, which will only show one set of the rows. You could use some more complex RLS, but I have kept it simple for this example.
The Index field is important as it is a link back to your main table, so you will need to create one if it doesn't already exist. This will allow any slicers or filters you have applied in your report page to impact this table.

I will say I have only used this on fairly small datasets as you can end up with a large number of rows.

------------------------------
Simon Brooks
BI Analyst


------------------------------