Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a data set with Applications and Groups, and the data is related by the Group ID with the respective Application IDs. Application IDs have cities.
Application City
1 | Delhi |
2 | Delhi |
3 | Delhi |
4 | Delhi |
5 | Delhi |
6 | Delhi |
7 | Delhi |
8 | Delhi |
9 | Delhi |
10 | Delhi |
11 | Delhi |
12 | Mumbai |
13 | Mumbai |
14 | Mumbai |
15 | Mumbai |
16 | Mumbai |
17 | Mumbai |
18 | Mumbai |
19 | Mumbai |
Application | Group |
1 | 1 |
2 | 1 |
3 | 1 |
4 | 1 |
5 | 1 |
6 | 1 |
7 | 1 |
8 | 1 |
9 | 1 |
10 | 1 |
11 | 1 |
12 | 1 |
13 | 1 |
14 | 1 |
15 | 1 |
16 | 1 |
17 | 1 |
18 | 1 |
19 | 1 |
I want to display a table with one Group ID per row, with all the cities and their count in one single cell.
Group IDCity
1 | Delhi (11), Mumbai(8) |
I've attached sample data and the desired output in the file here.
Thank you
Solved! Go to Solution.
Hi @sheap069
You want a Table visual to display it? So it is a measure
Result =
VAR T1 = GROUPBY(CityTable,CityTable[City],"COUNT",COUNTX(CURRENTGROUP(),[City]))
VAR T2 = ADDCOLUMNS(T1,"TEST",[City]&" ("&[COUNT]&")")
RETURN
CONCATENATEX(T2,[TEST],", ")
Hello @Vera_33
As a follow up to this I have some Applications with a large number of cities. Is it possible to only display the three with the highest counts?
Thank you
Hello @Vera_33 or anyone,
I would like to display just the top three cities that have the highest counts. Is this possible to do?
Another thing I would like to do is display the cities in order by the highest counts to the smallest.
Thank you
Hi @sheap069
Sorry, missed your message...modify the T2 a little bit
Result =
VAR T1 = GROUPBY(CityTable,CityTable[City],"COUNT",COUNTX(CURRENTGROUP(),[City]))
VAR T2 =TOPN(3, ADDCOLUMNS(T1,"TEST",[City]&" ("&[COUNT]&")"),[TEST],ASC)
RETURN
CONCATENATEX(T2,[TEST],", ")
Hi @Vera_33
That's okay. Thank you for the answer but it's still not ordering by the top count. For example, I have these counts for one Group ID
ADDR_CITY | Count of ADDR_CITY |
LAGOS | 15 |
PH | 4 |
ABEOKUTA | 1 |
BENIN | 1 |
KADUNA | 1 |
MBAISE | 1 |
OGUN STATE | 1 |
ONITSHA | 1 |
PRETORIA | 1 |
SOUTH AFRICA | 1 |
UYO | 1 |
But after applying your formula the top three showing are: KADUNA(1), BENIN(1), ABEOKUTA(1). I'm not sure how this order is generated when it should be Lagos(15), PH(4), ABEOKUTA(1).
Thanks again
Sorry @sheap069
Made a mistake...but the order is not good...
Result =
VAR T1 =TOPN(3, GROUPBY(CityTable,CityTable[City],"COUNT",COUNTX(CURRENTGROUP(),[City])),[COUNT],DESC)
VAR T2 = ADDCOLUMNS(T1,"TEST",[City]&" ("&[COUNT]&")")
RETURN
CONCATENATEX(T2,[TEST],", ")
Hi @sheap069
You want a Table visual to display it? So it is a measure
Result =
VAR T1 = GROUPBY(CityTable,CityTable[City],"COUNT",COUNTX(CURRENTGROUP(),[City]))
VAR T2 = ADDCOLUMNS(T1,"TEST",[City]&" ("&[COUNT]&")")
RETURN
CONCATENATEX(T2,[TEST],", ")
Perfect, thank you!!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |