Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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
User | Count |
---|---|
106 | |
86 | |
81 | |
74 | |
71 |
User | Count |
---|---|
112 | |
101 | |
96 | |
74 | |
67 |