CODEWITHSUNDEEP
excel-2013dax
Christopher Tso
Christopher Tso

Reputation: 339

Identifying Context Filter in DAX Measure

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

Upvotes: 0

Views: 352

Answers (1)

Christopher Tso
Christopher Tso

Reputation: 339

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

Upvotes: 0

Related Questions