Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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] )
)
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
19 | |
13 |
User | Count |
---|---|
125 | |
37 | |
29 | |
28 | |
24 |