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.
Greeting Everyone,
I am new to the community and facing very basic issue and any help on the issue is much appreciated!!
I have set of data where we have ID and Location column. Now one ID can be mapped to multiple location.
I need to get Countd of ID, where ID is mapped to more than 1 location in card.
Below are screen shot for ref:
1. PBI screnshot
2. Screen Shot of data:
ID | Location | Group | Div |
1 | Del | APAC | Jpn |
2 | Kol | APAC | Jpn |
3 | Bom | APAC | Jpn |
4 | Blr | APAC | Jpn |
5 | Chn | APAC | Aus |
2 | Bom | APAC | Aus |
4 | Del | APAC | Aus |
4 | Kol | APAC | Aus |
5 | Bom | APAC | Aus |
Thank you!
Solved! Go to Solution.
Hi,
thank you for sample data.
Sum of multi loc =
COUNTROWS (
FILTER (
SUMMARIZE (
Sheet1,
Sheet1[ID],
"Multi Loc", DISTINCTCOUNT ( Sheet1[Location] )
),
[Multi Loc] > 1
)
)
Result:
I hope this is what you wanted.
Proud to be a Super User!
Thank you so much!! It really work 🙂
However when I tired to create another table by keeping filter "Multi Loc" > 1, It just not working and became blank. Is there any way to fix it or achieve the soltion with above scenario where one can create table by having "Multi Loc" > 1. Any help to resolve the same is much appreciated.
Once again thank you!!
Screen shot of PBI:
Data:
Your visual is showing data by ID and if some ID has multiloc this columns has value: 1 not the "Count of location". If you want to use this measure to filter our the rows give it the rule: "IS 1".
Proud to be a Super User!
Can you paste here this table so I don't need to reproduce it? (sample data from screen)
Proud to be a Super User!
ID | Location | Group | Div |
1 | Del | APAC | Jpn |
2 | Kol | APAC | Jpn |
3 | Bom | APAC | Jpn |
4 | Blr | APAC | Jpn |
5 | Chn | APAC | Aus |
2 | Bom | APAC | Aus |
4 | Del | APAC | Aus |
4 | Kol | APAC | Aus |
5 | Bom | APAC | Aus |
Let me know if you have any other question. Thank you
Hi,
thank you for sample data.
Sum of multi loc =
COUNTROWS (
FILTER (
SUMMARIZE (
Sheet1,
Sheet1[ID],
"Multi Loc", DISTINCTCOUNT ( Sheet1[Location] )
),
[Multi Loc] > 1
)
)
Result:
I hope this is what you wanted.
Proud to be a Super User!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |