基于现有表条件创建Power BI页面级权限过滤表的SQL需求

需求逻辑梳理 #

• 所有comp_class中的OwnerID，自动获得Practice和Practice ROI两个页面的访问权限，每个权限单独占一行
• 所有doc_class中的OwnerID，在拥有上述机构页面权限的基础上，额外获得Doctor和Doctor ROI两个页面的访问权限
• 支持原表定期更新后，权限表自动同步更新

方法1：用DAX创建计算表 #

直接在Power BI的「建模」选项卡下新建计算表，粘贴以下DAX代码即可：

PageSecurity = 
// 定义医生专属页面集合
VAR DoctorPages = {"Doctor", "Doctor ROI"}
// 定义机构专属页面集合
VAR PracticePages = {"Practice", "Practice ROI"}
// 生成所有机构用户的页面权限行
VAR PracticePermissions = 
    CROSSJOIN(
        comp_class,
        SELECTCOLUMNS(PracticePages, "page", [Value])
    )
// 生成医生用户的额外权限行
VAR DoctorPermissions = 
    CROSSJOIN(
        doc_class,
        SELECTCOLUMNS(DoctorPages, "page", [Value])
    )
// 合并两类权限，自动完成权限叠加
RETURN
    UNION(PracticePermissions, DoctorPermissions)

说明：

• CROSSJOIN用来实现用户表和页面列表的笛卡尔积，自动生成每个用户对应每个页面的权限行
• 因为doc_class的所有OwnerID都在comp_class中，UNION合并后自然实现了医生用户同时拥有两类页面的权限
• 原表更新后，计算表会随Power BI的自动刷新同步更新

方法2：用Power Query创建查询表 #

如果习惯用Power Query处理数据，可按以下步骤操作（或直接粘贴简化代码）：

简化版Power Query代码 #

新建空白查询，粘贴以下代码并调整原表引用（若原表来自外部数据源，替换Excel.CurrentWorkbook()部分即可）：

let
    // 加载原表数据
    doc_class = Excel.CurrentWorkbook(){[Name="doc_class"]}[Content],
    comp_class = Excel.CurrentWorkbook(){[Name="comp_class"]}[Content],
    // 定义两类页面的列表
    DoctorPages = #table({"page"}, {{"Doctor"}, {"Doctor ROI"}}),
    PracticePages = #table({"page"}, {{"Practice"}, {"Practice ROI"}}),
    // 生成机构用户的权限行
    PracticePerm = Table.CrossJoin(comp_class, PracticePages),
    // 生成医生用户的额外权限行
    DoctorPerm = Table.CrossJoin(doc_class, DoctorPages),
    // 合并两类权限并排序（排序可选）
    Combined = Table.Combine({PracticePerm, DoctorPerm}),
    Sorted = Table.Sort(Combined,{{"OwnerID", Order.Ascending}, {"page", Order.Ascending}})
in
    Sorted

手动操作步骤（可选）：

1. 在Power Query编辑器中加载doc_class和comp_class
2. 新建空白查询，分别创建医生页面列表和机构页面列表
3. 对comp_class和机构页面列表做交叉合并，展开后保留OwnerID和page列
4. 对doc_class和医生页面列表做交叉合并，展开后保留OwnerID和page列
5. 追加上述两个权限表，关闭并应用即可

示例验证 #

用你提供的测试数据：

• doc_class：OwnerID 2、3
• comp_class：OwnerID 1、2、3、4

运行代码后将生成完全符合预期的权限表：

| OwnerID  | page         |
| -------- | ------------ |
| 1        | Practice     |
| 1        | Practice ROI |
| 2        | Doctor       |
| 2        | Doctor ROI   |
| 2        | Practice     |
| 2        | Practice ROI |
| 3        | Doctor       |
| 3        | Doctor ROI   |
| 3        | Practice     |
| 3        | Practice ROI |
| 4        | Practice     |
| 4        | Practice ROI |

内容的提问来源于stack exchange，提问作者Lisa M