Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DAX Expand / Collapse
Author
Message
Posted Monday, July 29, 2013 11:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 3, 2014 12:32 PM
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?
Post #1478685
Posted Wednesday, July 31, 2013 2:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 4, 2014 3:35 AM
Points: 367, Visits: 876
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
Post #1479332
Posted Wednesday, July 31, 2013 11:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 3, 2014 12:32 PM
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.
Post #1479620
Posted Thursday, August 1, 2013 3:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 4, 2014 3:35 AM
Points: 367, Visits: 876
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
Post #1479841
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse