Power BI中的默认筛选器-行级安全性未通过LOOKUPVALUE应用 [英] Default filter in Power BI - Row Level Security not applied through LOOKUPVALUE

问题描述

我需要基于用户在Power BI报表中选择一个默认值（位置）。

I have a requirement to have a 'default' value (location) picked in a Power BI report, based on the user.

I是针对Azure Analysis Services（表格模型1400）进行报告

I am reporting against Azure Analysis Services (tabular model 1400)

似乎在Power BI中实现默认值的方法是将值动态重命名为静态值，然后选择

It appears that the way to implement default values in Power BI is to dynamically rename a value to something static, and pick that static value as a filter.

所以

• 用户 Bob 具有默认位置 Location1 ，因此当他登录时，应该可以看到他的位置


• 用户 Joe 具有默认位置 Location2 ，因此应该在此位置对其进行过滤

• user Bob has default location Location1 so when he logs in he should be see his location
• user Joe has default location Location2 so he should be filtered on this location

诀窍在于，他们可以选择其他位置并查看如果他们喜欢

The trick being, they can optionally pick another location and see that if they like

我尝试的第一件事是使用 USERPRINCIPALNAME（）可怕ctly在行级表达式上，但我得到

The first thing I tried was using USERPRINCIPALNAME() directly on a row level expression but I get

...在计算中不支持USERNAME和USERPRINCIPALNAME函数表/列。这些函数只能用于度量或AllowedRowsExpression

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

RLS到另一个表，只是查找，如下所示：

So next I figured I would apply RLS to a different table and just look that up, as follows:

我从数据库中加载了一个名为 Location 列出了所有位置。

I have a table loaded from the database called Location that lists all locations.

我有一个名为 MyLocation 的独立表，它是位置。 MyLocation 使用以下DAX生成：

I have a standalone table called MyLocation, which is a copy of Location. MyLocation is generated using this DAX:

=SUMMARIZE(Location,Location[LocationKey],Location[Location Name])

（请注意，我也尝试过基于数据的表，而不是DAX）

(note I have also tried a table based on data, not on DAX)

MyLocation 还应用了动态行级安全性，如下所示：

MyLocation also has dynamic row level security applied like this:

=(
  [LocationKey]
  =
  LOOKUPVALUE(
      'Employee[LocationKey],
      'Employee'[UserPrincipalName],
      USERPRINCIPALNAME())
  )

当我在Power BI中查看 MyLocation 时，可以看到已应用RLS-只能看到一个位置。这与位置相反，在这里我可以看到所有位置（未定义RLS）

When I look at MyLocation in Power BI I can see the RLS is applied - only one location can be seen. This is in contrast to Location, where I can see all locations (which has no RLS defined)

下一个我在 Location 中添加了一个列（行级表达式）以选择使用行级安全性评估的单个位置：

Next I added a column (row level expression) in Location to go and pick out this 'single' location that is evaluated using row level security:

=LOOKUPVALUE(
    'MyLocation'[Location Name],
    'MyLocation'[LocationKey],
    'Location'[LocationKey]
)

它与LocationKey匹配，但是对于已通过RLS删除的记录，

It's matching on LocationKey, but for records that have been removed via RLS, there should be no match.

但是，当我测试最后一列时，RLS被忽略，所有位置都通过。

在Power BI中，我可以看到两个表在我面前：

I can see both tables right in front of me in Power BI:

MyLocation 此：
（应用了RLS）

MyLocation looks like this: (RLS is applied)

Location       
==========================
Location 3      Location 3

位置看起来像这样：
（未应用RLS，但为什么其他位置不为空白？）

Location looks like this: (No RLS applied but why aren't the other locations blank?)

Location       LookupValue            
==========================
Location 1      Location 1
Location 2      Location 2
Location 3      Location 3
Location 4      Location 4
Location 5      Location 5
...........
....

我希望位置看起来像这样：

Location       LookupValue            
==========================
Location 1      blank
Location 2      blank
Location 3      Location 3
Location 4      blank
Location 5      blank
...........
....

因此，无论您使用什么技巧，您都无法使用USERPRINCIPALNAME（）

So it appears no matter what trick you use, you really can't use USERPRINCIPALNAME() on a row.

我也尝试过定义 USERPRINCIPALNAME（）作为度量，并使用它失败（现在不记得该错误了但是我也会重试）

I've also tried defining USERPRINCIPALNAME() as a measure and using that but that also failed (don't recall the error right now but I'll also retry it)

我也尝试过使用独立参数表使用 SELECTEDVALUE ，但参数表中的过滤值永远不会出现。尽管过滤了该表，但 ISFILTERED 始终返回false。

I also tried using a 'standalone parameter' table to switch RLS on and off using SELECTEDVALUE but the filtered value in the parameter table never appears. ISFILTERED always returns false despite that table being filtered.

推荐答案

我现在明白你的意思了。 LOOKUPVALUE 似乎具有未过滤的表访问权限，绕过了RLS。我建议将此错误报告给Microsoft。

I see what you mean now. LOOKUPVALUE appears to have unfiltered access to your table, bypassing RLS. I'd suggest reporting this to Microsoft as a bug.

在您的报告中，建议使用以下形式的度量：

In your report, I'd suggest using measures of this form:

Measure =
VAR EmployeeLocation =
        LOOKUPVALUE(Employee[LocationKey],
                    Employee[UserPrincipalName],
                     USERPRINCIPALNAME())
RETURN IF(ISFILTERED(Location[Location]),
           <expression>,
           CALCULATE(<expression>,
               FILTER(Location, Location[LocationKey] = EmployeeLocation)))

这样，它应该默认为计算 EmployeeLocation ，当位置保留未过滤状态，否则将正常运行。

That way it should default to calculating values for EmployeeLocation when Location is left unfiltered and will behave normally otherwise.

这篇关于Power BI中的默认筛选器-行级安全性未通过LOOKUPVALUE应用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！