Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I am stuck with a measure that should display the total of IDs for each category, but without showing the category.
Currently, I am able to display the column "Total ID by Country" using the following measure:
Total ID by Country =
CALCULATE(
COUNT(Mentions[ID]),
ALLEXCEPT(
Mentions,
Market[Country] // Dimension table
)
)
ID | Country | Count ID | Total ID by Country |
1 | US | 1 | 3 |
2 | US | 1 | 3 |
3 | US | 1 | 3 |
4 | FR | 1 | 7 |
5 | FR | 1 | 7 |
6 | FR | 1 | 7 |
7 | FR | 1 | 7 |
8 | FR | 1 | 7 |
9 | FR | 1 | 7 |
10 | FR | 1 | 7 |
The problem is, as long as I remove the "Country" column, I get this result (column country should not be displayed):
ID | Count ID | Total ID by Country | |
1 | 1 | 10 | |
2 | 1 | 10 | |
3 | 1 | 10 | |
4 | 1 | 10 | |
5 | 1 | 10 | |
6 | 1 | 10 | |
7 | 1 | 10 | |
8 | 1 | 10 | |
9 | 1 | 10 | |
10 | 1 | 10 |
The goal is to get this (Country column should not be displayed):
ID | Count ID | Total ID by Country |
1 | 1 | 3 |
2 | 1 | 3 |
3 | 1 | 3 |
4 | 1 | 7 |
5 | 1 | 7 |
6 | 1 | 7 |
7 | 1 | 7 |
8 | 1 | 7 |
9 | 1 | 7 |
10 | 1 | 7 |
I don't think I can use a calculated column as this measure is sliced by other dimensions then.
Many thanks in advance for your help.
Solved! Go to Solution.
It will be either this or something close to it (depending on which fields make up the table)
Total ID by Country =
CALCULATE(
COUNT(Mentions[ID]),
FILTER(ALL(Mentions),
Mentions[Country] = MAX(Mentions[Country] )
)
)
@Anonymous , I doubt that will work without a country in context. But try this
Total ID by Country =
CALCULATE(
COUNT(Mentions[ID]),
filter(
allselected(Mentions),
Market[Country] =max(Market[Country] ) // Dimension table
)
)
It will be either this or something close to it (depending on which fields make up the table)
Total ID by Country =
CALCULATE(
COUNT(Mentions[ID]),
FILTER(ALL(Mentions),
Mentions[Country] = MAX(Mentions[Country] )
)
)