SQLServerCentral » Data Warehousing » Analysis Services » DAXInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralTue, 25 Apr 2017 02:54:38 GMT20DAXhttps://www.sqlservercentral.com/Forums/FindPost1478685.aspxI trying to create a type of inverse filter using DAX. For Ex:
[b][u]Filter Table[/u][/b]
[u]Column1[/u]
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 [i]remove[/i] A from the result, and pass B and C instead.
Make sense? Any thoughts?
Thu, 01 Aug 2013 03:10:55 GMTTerry TroisiRE: DAXhttps://www.sqlservercentral.com/Forums/FindPost1479841.aspxHi 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],[b]ExcludeFilterTable[/b][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
-MattThu, 01 Aug 2013 03:10:55 GMTLowfarRE: DAXhttps://www.sqlservercentral.com/Forums/FindPost1479620.aspxThanks 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.Wed, 31 Jul 2013 11:39:20 GMTTerry TroisiRE: DAXhttps://www.sqlservercentral.com/Forums/FindPost1479332.aspxHi 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
-MattWed, 31 Jul 2013 02:09:54 GMTLowfar