Identifying Context Filter in DAX Measure

Question

How do I create a DAX measure in Power Pivot that shows the column name that is used as the lowest level filter context for each row in the Pivot Table?

I have 2 tables which are related on the Account column.

Table 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

Table BalSheet

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

DAX Measure for calculating the context filter

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

Result with Single Hierarchy

The measure works fine with a single hierarchy. Here's what the pivot table looks like (in 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

Result with Mixed Hierarchy

When I mix 2 hierarchies together (e.g. both Model and Business), then the measure keeps showing "Model_L1" when it should show "Business_L2" or "Business_L3". Here's what the pivot table looks like (in 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

This is what I want the pivot table to look like:

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

It is not possible to understand problem you are facing from above description. Can you provide data model and hierarchy information? — Abhijeet Nagre
I tried HASONEVALUE() but that doesn't give the desired result when there is only one value that is being rolled up to the current context. Anyway, the answer came to my mind while I was providing more information to my question. — Christopher Tso

Christopher Tso Christopher Tso · Accepted Answer · 2015-02-28T06:14:24

The reason why the [CurrentLevel] measure always evaluates to "Model_L1" when you have "Model_L1", "Business_L2", "Business_3" as the pivot table row fields is because the "Model_L1" sits above "Business_X" in the SWITCH() statement. This should give you the desired result:

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

Identifying Context Filter in DAX Measure

1 Answers