Post reply

DAX

  • July 29, 2013 at 11:39 am

    #149907

    I trying to create a type of inverse filter using DAX. For Ex:

    Filter Table

    Column1

    A

    B

    C

    If a user filters by A, then a can catch that in my fact table like CALCULATE(SUM([SalesAmount]),'Filter Table').

    But what I want to do is remove A from the result, and pass B and C instead.

    Make sense? Any thoughts?

  • July 31, 2013 at 2:09 am

    #1637258

    Hi Terry

    Possibly try the following:

    CALCULATE(SUM(Sales[Sales]),ALL('FilterTable'[Column1])) - SUM(Sales[Sales])

    Using your example - user selecting 'A':

    The CALCULATE will return Sales (A+B+C) ignoring the user filter. The second SUM (after the minus sign) will be affected by the user filter so will be Sales for A only. Subtracting the two will return Sales (B+C).

    Hope this makes sense. I'd be interested to hear if you found another solution to this problem.

    Thanks

    -Matt

  • July 31, 2013 at 11:39 am

    #1637499

    Thanks Matt. I like it.

    I think I had been more focused on removing A from the row labels/column labels list in a pivot table, which this solution won't do, but displaying a zero next to A in the values section should be a good workaround.

    If I revisit this in a few days and figure out how to remove A altogether I'll post a reply.

  • August 1, 2013 at 3:10 am

    #1637695

    Hi Terry

    No worries, think I misread your requirement. If I've got it right the user selects 'A' and only wants to see the row labels for B + C.

    To do this I've created another Filter table (called ExcludeFilterTable ) this has an inactive relationship with the sales table. I've then created a Measure for Sales using the following DAX Expression:-

    Sales:=CALCULATE(

    SUM(Sales[Sales]),

    CALCULATETABLE(SUMMARIZE(Sales,FilterTable[Column1])

    ),

    FILTER(

    FilterTable,

    CALCULATE(

    COUNTROWS(Sales),

    USERELATIONSHIP(Sales[Column1],ExcludeFilterTable[Column1])

    )=0

    )

    )

    To construct the Pivot table, place the ExcludedFilterTable into the pivot table filter, place FilterTable into Rows and place Sales(measure created using the above DAX) in values

    The value selected in the Pivot table Filter should be excluded from the Row labels.

    I've tested this in Excel 2013 Powerpivot and it seems to work well, all be it with a very small data set.

    Hopefully this will be a good starting point for you.

    Cheers

    -Matt

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply