Measure with own department vs. total company (not RLS!)

[MiKeZZa]

Hi all,

I want to have a Power BI report with 2 tiles:

Total sales: 100

Sales own departments: 26

So I don't want to have RLS. I want a label which tells the user which departments are his departments but he must be able to see the whole dataset. Example:

I've tried many things, just like this datamodel:

This works great if I filter hard on upn in rights equals mail@domain.com. But that's off course not what I want, I want to have this based on userprincipalname().

My idea was to create a field 'is own department' in Department (which contains "Yes" or "No" and filter my measure on that) with this code:

is_own_department = if(USERPRINCIPALNAME() == related(Rights[UPN]); "Yes"; "No")

but that's not possible because of that department is 1:N to rights and I can't use userprincipalname in a column:

CUSTOMDATA, USERNAME, USERCULTURE and USERPRINCIPALNAME functions are not supported in calculated tables/columns. These functions may only be used in Measures or in the AllowedRowsExpression.

So is there anybody with an great idea? I am struggling for a few days already.....

Example PBIX: https://easyupload.io/d2b3cd

[v-frfei-msft]

Hi @MiKeZZa ,

To use a slicer and a measure to work on it.

Total sales = CALCULATE(SUM('Fact'[salesamount]),ALL('Fact'))

Also you can use this measure to work on it UPN instead of calculated column.

is_own_department_ = var upn = CALCULATE(MAX(Rights[upn]),FILTER(Rights,Rights[departmentid] = MAX(Department[departmentid])))
return
if(USERPRINCIPALNAME() = upn, "Yes", "No")

For more details, please check the pbix as attached.

[MiKeZZa]

Hi @v-frfei-msft , thank you for your time and the PBIX. But I don't think this is the solution or I don't understand what you mean. It's great that I can have Is_own_department_ with yes or no. That was my initial question, but because of that it is a measure and not a column (exactly the problem I already experienced) you can't use it in a filter to filter on is_own_department_ = 'Yes'... Or am I indeed mistaking your point?

[v-frfei-msft]

Hi @MiKeZZa ,

Here we go.

Measure = 
VAR upn = "user1@domain.com"
VAR I =
    CALCULATETABLE (
        VALUES ( Rights[departmentid] ),
        FILTER ( Rights, Rights[upn] = upn )
    )
RETURN
    CALCULATE (
        SUM ( 'Fact'[salesamount] ),
        FILTER ( 'Fact', 'Fact'[departmentid] IN I )
    )

[d_gosbell]

A measure like the following should do what you want

My Department Sales = 
VAR _userDepts = CALCULATETABLE(values(Rights[departmentid]), Rights[upn] = USERPRINCIPALNAME() )
RETURN CALCULATE( SUM('Fact'[salesamount]) , TREATAS( _userDepts, Department[departmentid]) )

 Technically you don't need any the relationship to the rights table for the above technique to work. So unless you are using it for other purposes, removing it might give you a minor performance gain.

[MiKeZZa]

Hi @d_gosbell 

Wow, this is great! This is exactly what I asked for.

But because of that it lacks the relation it makes me to make a copy of every measure that I have. Is there any other way of achieving this behaviour for the regular calculations, but with a filter or anything?

If the answer is no I'm very happy with this, so don't worry 🙂

But if I will be able (by example) to have a page with 20 measures and 1 slicer which says; show only own departments yes/no then that would be a consideration too for me and my team.

And I now realize that my datamodel may be in SSAS Tabular 2016 instead of Power BI; then treatas is not supported. So that can be a setback for our situation. Your solution remains great.

Edit @v-frfei-msft ; This is also a great solution. It works great. But for you the same questions:
1) Is there a way to achieve this without creating separate measure. So that I will be able (by example) to have a page with 20 measures and 1 slicer which says; show only own departments yes/no then that would be a consideration too for me and my team.

2) Is there a way to get this working in SSAS 2016? So without the IN clause which isn't supported as I can see now.

[d_gosbell · Accepted solution]

---

@MiKeZZa wrote:

But because of that it lacks the relation it makes me to make a copy of every measure that I have. Is there any other way of achieving this behaviour for the regular calculations, but with a filter or anything?

If the answer is no I'm very happy with this, so don't worry 🙂

Sorry, but I don't think there is any alternative other than baking this in to your measures. But there is an inbetween approach where you could create a set of base measures which can be toggled and you could then build other measures off these base measures. So if you created a [Sales Amount] measure with logic like the measure below and you had a "My Department" table with a [My Department] column which had values of "My Department" and "All Departments" you could the use a slicer to switch between the All Departments and My Department views and you could build other measures (eg. YTD Sales, etc) on top of the measure below without having to repeat the "My Department" logic

Measure = 
VAR upn = "user1@domain.com"
VAR I =
    CALCULATETABLE (
        VALUES ( Rights[departmentid] ),
        FILTER ( Rights, Rights[upn] = upn )
    )
VAR isMyDept = 
    IF( HASONEVALUE( 'My Department'[My Department] ) , 
        VALUES('My Department'[My Department]) = "My Department", 
        FALSE() 
    )
RETURN
    IF( isMyDept ,
    CALCULATE (
        SUM ( 'Fact'[salesamount] ),
        I )
    ),
    SUM ( 'Fact'[salesamount] )
    )

But if I will be able (by example) to have a page with 20 measures and 1 slicer which says; show only own departments yes/no then that would be a consideration too for me and my team.

Note that in SSAS 2019 there is a feature called calculation groups which could possibly be used to dynamically alter apply this logic based off a single slicer which would be worth looking at if you upgrade at some point in the future.

---

2) Is there a way to get this working in SSAS 2016? So without the IN clause which isn't supported as I can see now.

---

Because you have a bi-directional filter from Rights to Departments you should be able to alter the answer by @v-frfei-msft to eliminate the IN keyword and then I think the following should be valid syntax for 2016. (I also used this variation in the slicer based measure earlier)

Measure = 
VAR upn = "user1@domain.com"
VAR I =
    CALCULATETABLE (
        VALUES ( Rights[departmentid] ),
        FILTER ( Rights, Rights[upn] = upn )
    )
RETURN
    CALCULATE (
        SUM ( 'Fact'[salesamount] ),
        I
    )

[MiKeZZa]

Yes, this seams to be the solutions. It works great in Power BI and contains nothing that I can't use in SSAS 2016. But it's not working there. I'll continue with investigating this, but I'm sure this will be the solution!!!

I wanted to let you know that it works in SSAS fine now! 🙂