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

问题描述

TL;DR:当对具有行级安全性的表使用 LOOKUPVALUE() 时，不会应用 RLS 并且会看到所有值

我需要根据用户在 Power BI 报告中选择一个默认"值(位置).

我正在报告 Azure 分析服务(表格模型 1400)

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

所以

• 用户 Bob 有默认位置 Location1，所以当他登录时，他应该会看到他的位置
• 用户 Joe 有默认位置 Location2，所以他应该在这个位置被过滤

诀窍在于，他们可以随意选择另一个位置，看看他们是否喜欢

我尝试的第一件事是直接在行级表达式上使用 USERPRINCIPALNAME() 但我得到了

<块引用><块引用>

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

所以接下来我想我会将 RLS 应用到不同的表并进行查找，如下所示:

我有一个从名为 Location 的数据库加载的表，其中列出了所有位置.

我有一个名为 MyLocation 的独立表，它是 Location 的副本.MyLocation 是使用此 DAX 生成的:

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

(请注意，我也尝试过基于数据而非 DAX 的表)

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

<代码>=([位置键]=查找值('员工[LocationKey],'员工'[UserPrincipalName],USERPRINCIPALNAME()))

当我在 Power BI 中查看 MyLocation 时，我可以看到 RLS 已应用 - 只能看到一个位置.这与 Location 形成对比，我可以在其中看到所有位置(未定义 RLS)

接下来，我在 Location 中添加了一个列(行级表达式)来挑选使用行级安全性评估的这个单一"位置:

=LOOKUPVALUE('我的位置'[位置名称],'我的位置'[位置键],'位置'[位置键])

在 LocationKey 上匹配，但是对于通过 RLS 删除的记录，应该没有匹配.

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

我可以在 Power BI 中看到我面前的两张表:

MyLocation 如下所示:(应用RLS)

位置===========================地点 3 地点 3

位置如下所示:(没有应用 RLS，但为什么其他位置不是空白?)

位置查找值===========================位置 1 位置 1位置 2 位置 2地点 3 地点 3地点 4 地点 4地点 5 地点 5............……

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

位置查找值===========================位置 1 空白位置 2 空白地点 3 地点 3位置 4 空白位置 5 空白............……

所以无论你使用什么技巧，你真的不能连续使用 USERPRINCIPALNAME().

我也尝试将 USERPRINCIPALNAME() 定义为度量并使用它，但也失败了(现在不记得错误，但我也会重试)

我还尝试使用独立参数"表通过 SELECTEDVALUE 打开和关闭 RLS，但参数表中的过滤值从未出现.ISFILTERED 始终返回 false，尽管该表已被过滤.

解决方案

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

在您的报告中，我建议使用这种形式的度量:

测量 =VAR 员工位置 =LOOKUPVALUE(员工[LocationKey],员工[UserPrincipalName]，USERPRINCIPALNAME())返回 IF(ISFILTERED(位置[位置]),<表达式>,计算(<表达式>,过滤器(位置，位置 [LocationKey] = EmployeeLocation)))

这样，当 Location 未过滤时，它应该默认计算 EmployeeLocation 的值，否则将正常运行.

TL;DR: When using LOOKUPVALUE() against a table with Row Level Security, the RLS is not applied and all values are seen

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

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

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.

So

• 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

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

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

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

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

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])

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

MyLocation also has dynamic row level security applied like this:

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

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)

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]
)

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

However when I test this final column, RLS is ignored, and all locations come through.

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

MyLocation looks like this: (RLS is applied)

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

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
...........
....

I expect Location to look like this:

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

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

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)

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.

解决方案

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)))

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

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