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.
Hello!
I am struggling to figure out a way to summarize and visualise an ask that involves determining distinct counts on multiple columns. Lets say i have a list of Employee #, Country and City and my ultimate goal is to identify how many employees traveled to 1 county, 2 counties, 3, 4 and onwards (for this example lets say there are 5 countries).
Emplyee # | Country | City |
1 | France | Paris |
1 | Germany | Berlin |
1 | Italy | Milan |
1 | Italy | Rome |
1 | Poland | Warsaw |
1 | Greece | Athens |
2 | France | Paris |
2 | Germany | Berlin |
2 | Germany | Munich |
2 | Greece | Athens |
3 | Germany | Berlin |
3 | Italy | Milan |
3 | Italy | Rome |
3 | Greece | Athens |
4 | Italy | Milan |
In the example table above, the result would be :
Traveled to 1 country - 1 (employee 4)
Traveld to 2 countries - 0
Traveled to 3 countries - 2 (employee 2 & 3)
Traveled to 4 countries - 0
Traveled to 5 countries - 1 (employee 1)
I then want to be able to create a visual that would look something like this
I am struggling to figure out the correct measures to develop in order to capturing this informaiton and visualze. Any help you have would be greatly appreciated.
Thank you!
Solved! Go to Solution.
hello Jgrohows
Is this the output wanted ?
I creat one column and one mesure:
No of countries =
-- Column
var _name = 'Table'[Name]
var _value =
CALCULATE(
DISTINCTCOUNT('Table'[Country]),
ALL('Table'),'Table'[Name] = _Name
)
return
_value
Total of persons =
-- Measure
DISTINCTCOUNT('Table'[Name])
check the solution in this LINK
Best regards
Bruno Costa | Solution Supplier
Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 👍
Take a look at the blog: PBI Portugal
Best regards
Bruno Costa | Super User
Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!!
Take a look at the blog: PBI Portugal
hello Jgrohows
Is this the output wanted ?
I creat one column and one mesure:
No of countries =
-- Column
var _name = 'Table'[Name]
var _value =
CALCULATE(
DISTINCTCOUNT('Table'[Country]),
ALL('Table'),'Table'[Name] = _Name
)
return
_value
Total of persons =
-- Measure
DISTINCTCOUNT('Table'[Name])
check the solution in this LINK
Best regards
Bruno Costa | Solution Supplier
Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 👍
Take a look at the blog: PBI Portugal
Best regards
Bruno Costa | Super User
Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!!
Take a look at the blog: PBI Portugal
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 |
---|---|
84 | |
83 | |
64 | |
60 | |
55 |
User | Count |
---|---|
179 | |
108 | |
105 | |
71 | |
70 |