Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I am very new to Power BI and still exploring it. Suppose we have the following Excel table
Name Country Comp1 Col2 Col3
A X cs ps
B Y ps ip cs
C Z ip
I want to count how many there is a cs ,ps and ip.
And in the visual when i press on CS i want the map representing the Country to highlight the countries that includes the CS
Many thanks
Solved! Go to Solution.
Hi @abukapsoun,
If this is the only problem, I would suggest to use a new function "DISTINCTCOUNT".
Measure = DISTINCTCOUNT ( Table1[Name] )
Because only keeping one row would lead another problem that the connection between "country" and "value" would be broken. (the data in red rectangle belongs to no country.)
Best Regards!
Dale
Hi @abukapsoun,
Firstly, we need to import the data into Power BI from excel.
Secondly, select "Name" and "Country" with "Ctrl" key. Unpivot three columns "Comp1, Col2, Col3".
Thirdly, create a measure.
Count = COUNT(Table1[Value])
I attached the file, you can try it yourself. https://1drv.ms/u/s!ArTqPk2pu-BkgQt-qNdTYX9b5zow
Best Regards!
Dale
Thank you very much Dale..
The unpivot trick worked, but created me another problem.
When unpivotting, it will create extra rows but duplication. So if I unpivot Col1 2 3, and I want to count how many person there is in UK, the value would be 3 instead of 1.
Is there a way I can overcome this problem? Like when unpivotting, only the new created column is filled with values and not the whole row.
Thanks again
Hi @abukapsoun,
If this is the only problem, I would suggest to use a new function "DISTINCTCOUNT".
Measure = DISTINCTCOUNT ( Table1[Name] )
Because only keeping one row would lead another problem that the connection between "country" and "value" would be broken. (the data in red rectangle belongs to no country.)
Best Regards!
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |