Forum Discussion
Help with Calculate arguments
I have a table visual with a measure, and one of the columns in the visual needs to be ignored in caulcating the measure. I can use the ALL argument, but then it also ignores a slicer I have on the report (because they are consuming the same table column)
Is there a way to force the measure to ignore the column in the visual, but not the slicer...?
5 Replies
Hi Anonymous
Try to use ALLEXCEPT instead of ALL, put the column "a" which is used in a slicer to the ALLEXCEPT like this "ALLEXCEPT(table,table[a])
Best Regards
Maggie
Unfortunately, that doesn't work.
Data Table:
ID Age Country 1 33 Canada 2 44 USA 3 55 USA 4 43 Canada 5 54 USA 6 75 America 7 12 America 8 47 Canada 9 23 USa 10 38 Canada Mapping Table
Country Mapped Canada Canada USA USA America USA There is a relationship between Country for both tables. I have one measure: Measure = Average(Sheet1[Age])
Here is the Table Visual with the measure as is:
So I adjust the measure to be: Measure = CALCULATE(Average(Sheet1[Age]),all(Sheet1[ID]))Now it looks likes this:
You can see how it now shows duplicates for every value in the mapping table, and the average age is for for mapped country. So I try: Measure = CALCULATE(Average(Sheet1[Age]),allexcept(Sheet1,Sheet1[Country]))
The average age is correct, but the duplicates are still there.
What's your desired output? It looks like the measure is working
