识别DAX度量中的上下文过滤器

Question

如何在Power Pivot中创建一个DAX度量值，该度量值显示用作Pivot表中每行的最低级别筛选器上下文的列名？

我有两张与"帐户"列相关的表。

表DimAccount

Account,Model_L1,Model_L2,Model_L3,Business_L1,Business_L2,Business_L3
1,Working Capital,Trade Payables,Trade Creditors,Opex,Technology,Managed Services
2,Investing,Capex Creditors and Accruals,Capex Trade Creditors,Capex,Capex Creditors,Intangible Asset Creditors
3,Working Capital,Trade Receivables,Prepaid Debtors,Receivables,Prepaid Receipts,Prepaid MBB Receipts

表资产负债表

Account,Value
1,50
2,60
3,40

用于计算上下文过滤器的DAX度量

MEASURE BalSheet[CurrentLevel]
=SWITCH(TRUE(),
    CALCULATE(COUNTROWS(VALUES(DimAccount[Model_L3])),
        ALLEXCEPT(DimAccount, DimAccount[Model_L3])) = 1,
        "Model_L3",
    CALCULATE(COUNTROWS(VALUES(DimAccount[Model_L2])),
        ALLEXCEPT(DimAccount, DimAccount[Model_L2])) = 1,
        "Model_L2", 
    CALCULATE(COUNTROWS(VALUES(DimAccount[Model_L1])),
        ALLEXCEPT(DimAccount, DimAccount[Model_L1])) = 1,
        "Model_L1",
    CALCULATE(COUNTROWS(VALUES(DimAccount[Business_L3])),
        ALLEXCEPT(DimAccount, DimAccount[Business_L3])) = 1,
        "Business_L3",
    CALCULATE(COUNTROWS(VALUES(DimAccount[Business_L2])),
        ALLEXCEPT(DimAccount, DimAccount[Business_L2])) = 1,
        "Business_L2",
    CALCULATE(COUNTROWS(VALUES(DimAccount[Business_L1])),
        ALLEXCEPT(DimAccount, DimAccount[Business_L1])) = 1,
        "Business_L1",
    "All"
)

单一层次结构的结果

该措施适用于单一层次结构。以下是数据透视表的样子（csv）。

Model_L1,Model_L2,Model_L3,CurrentLevel
Investing,Capex Creditors and Accruals,Capex Trade Creditors,Model_L3
Investing,Capex Creditors and Accruals,,Model_L2
Investing,,,Model_L1
Working Capital,Trade Payables,Trade Creditors,Model_L3
Working Capital,Trade Payables,,Model_L2
Working Capital,Trade Receivables,Prepaid Debtors,Model_L3
Working Capital,Trade Receivables,,Model_L2
Working Capital,,,Model_L1
Grand Total,,,All

混合层次结构的结果

当我将两个层次结构混合在一起（例如，Model和Business）时，度量值会在应该显示"Business_L2"或"Business_L3"的时候继续显示"Model_L1"。以下是数据透视表的样子（csv）。

Model_L1,Business_L2,Business_L3,CurrentLevel
Investing,Capex Creditors,Intangible Asset Creditors,Model_L1
Investing,Capex Creditors Total,,Model_L1
Investing,,,Model_L1
Working Capital,Prepaid Receipts,Prepaid MBB Receipts,Model_L1
Working Capital,Prepaid Receipts Total,,Model_L1
Working Capital,Technology,Managed Services,Model_L1
Working Capital,Technology Total,,Model_L1
Working Capital,,,Model_L1
Grand Total,,,All

这就是我希望数据透视表的样子：

Model_L1,Business_L2,Business_L3,CurrentLevel
Investing,Capex Creditors,Intangible Asset Creditors,Business_L3
Investing,Capex Creditors Total,,Business_L2
Investing,,,Model_L1
Working Capital,Prepaid Receipts,Prepaid MBB Receipts,Business_L3
Working Capital,Prepaid Receipts Total,,Business_L2
Working Capital,Technology,Managed Services,Business_L3
Working Capital,Technology Total,,Business_L2
Working Capital,,,Model_L1
Grand Total,,,All

Answer 1

当您将"Model_L1"、"Business_L2"one_answers"Business_3"作为透视表行字段时，[CurrentLevel]度量值之所以总是计算为"Model_L2"，是因为SWITCH（）语句中"Model_Ll"位于"Business_X"之上。这应该会给你想要的结果：

=SWITCH(TRUE(),
    CALCULATE(COUNTROWS(VALUES(DimAccount[Model_L3])),
        ALLEXCEPT(DimAccount, DimAccount[Model_L3])) = 1,
        "Model_L3",
    CALCULATE(COUNTROWS(VALUES(DimAccount[Business_L3])),
        ALLEXCEPT(DimAccount, DimAccount[Business_L3])) = 1,
        "Business_L3",
    CALCULATE(COUNTROWS(VALUES(DimAccount[Model_L2])),
        ALLEXCEPT(DimAccount, DimAccount[Model_L2])) = 1,
        "Model_L2", 
    CALCULATE(COUNTROWS(VALUES(DimAccount[Business_L2])),
        ALLEXCEPT(DimAccount, DimAccount[Business_L2])) = 1,
        "Business_L2",
    CALCULATE(COUNTROWS(VALUES(DimAccount[Model_L1])),
        ALLEXCEPT(DimAccount, DimAccount[Model_L1])) = 1,
        "Model_L1",
    CALCULATE(COUNTROWS(VALUES(DimAccount[Business_L1])),
        ALLEXCEPT(DimAccount, DimAccount[Business_L1])) = 1,
        "Business_L1",
    "All"
)