Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DAX


Author
Message
Terry Troisi
Terry Troisi
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 143
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?
Lowfar
Lowfar
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 894
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
Terry Troisi
Terry Troisi
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 143
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.
Lowfar
Lowfar
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 894
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])
   Wink,
   FILTER(
      FilterTable,
      CALCULATE(
         COUNTROWS(Sales),
         USERELATIONSHIP(Sales[Column1],ExcludeFilterTable[Column1])
      Wink=0
   Wink
)

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search