Forum Discussion
Helper IExclusion List in DAX
I am trying to create a calculation to exlclude a list of characters from the summed calculation within a text string. The example below is a very short list to the thousands of rows I am actually trying to do this calculation on. I am unable to apply the out-of-the box filter or go into the query and edit the data because I am working in a locked dataset.
In the above example, I want to exclude nonproductive labor (labor with cost id's of .NF and .MT) to show the Cost Assoc of the productive labor (everything else). My expected result is 35,499 when applying filters to the table on the far right. Below is the DAX I used so far which is still returing the same result as the table, 62,877.
0_Productive Labor =
CALCULATE(SUM('CostCharged'[Cost Assoc]),
'CostCharged'[WbsCode] <> ".NF",
'CostCharged'[WbsCode] <> ".MT"
)
On the larger example, I tried to do something like this to include the values I actually wanted, but it was still returning an error. Plus the exclusion list is smaller than the inclusion list so I thought it would be easier to just try and exclude the values?
VAR _Filter =
FILTER (
ALLSELECTED ( 'CostCharged' ),
'CostCharged'[WbsCode]
IN {
".1",
".BRNCH",
".HANGS",
".NSLAB",
".OHEAD",
".RACK",
".RISER",
".SKID",
".SLEEV",
".UG"
}
)
Any thoughts on how to work around this? I'll eventually use this list to build other DAX Measurements up. Thank you in advance.
ExlusionOfStrings = CALCULATE(SUMX(FILTER('Exlusion', NOT (CONTAINSSTRING(Exlusion[String],".NF") || CONTAINSSTRING(Exlusion[String],".MT"))), 'Exlusion'[Value]))
5 Replies
- vojtechsima
Super User
Hi, thomazinh,
try something like this:0_Productive Labor = CALCULATE(SUMX(FILTER('CostCharged', NOT('CostCharged'[WbsCode] IN {".NF", ".MT"}), 'CostCharged'[Cost Assoc])))
thomazinhvojtechsima - that didn't work for me either. I had to add the SUM(CostCharged[Cost Assoc]) to get around an error. Its returning a value of 754,524 and not 35,499.
- vojtechsima
Hi, thomazinh ,
my bad, I missed one ")",
I tested it with sample data and it should work, see the following:ExlusionOfStrings = CALCULATE(SUMX(FILTER('Exlusion', NOT('Exlusion'[String] IN {".NF", ".MT"})), 'Exlusion'[Value]))The Card Visual is representing the measure, the tables are for clarity.