Forum Discussion
Help with Calculate arguments
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
If you look at the last visual, it gives the impression that ID 1 is in both countries. I want to display the average of all ages in the data, regardless of country, but I also want the report user to be able to see what country they ID belongs to. I just can't seem to make it work.
My second requirement, which adds complexity to all this, is a slicer FOR country. When Canada is selected, the table visual will ONLY show the average age for Canada, and will ONLY show Canadian IDs. When both countries are selected, it will take the average across both countries and show IDs in both countries.
I get that i can use a 2nd visual for the average, but this is just a simplified example to illustrate my problem. I have to have the measure in the table.
Anonymous
Check this post by marcorusso
https://www.sqlbi.com/articles/using-or-conditions-between-slicers-in-dax/
