Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sheap069
Helper III
Helper III

Get Values and Counts in Table

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

1Delhi
2Delhi
3Delhi
4Delhi
5Delhi
6Delhi
7Delhi
8Delhi
9Delhi
10Delhi
11Delhi
12Mumbai
13Mumbai
14Mumbai
15Mumbai
16Mumbai
17Mumbai
18Mumbai
19Mumbai

 

ApplicationGroup
11
21
31
41
51
61
71
81
91
101
111
121
131
141
151
161
171
181
191

 

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

1Delhi (11), Mumbai(8)

 

I've attached sample data and the desired output in the file here

 

Thank you

1 ACCEPTED SOLUTION
Vera_33
Resident Rockstar
Resident Rockstar

Hi @sheap069 

 

You want a Table visual to display it? So it is a measure

 

Vera_33_0-1614931749099.png

Result = 
VAR T1 = GROUPBY(CityTable,CityTable[City],"COUNT",COUNTX(CURRENTGROUP(),[City]))
VAR T2 = ADDCOLUMNS(T1,"TEST",[City]&" ("&[COUNT]&")")
RETURN
CONCATENATEX(T2,[TEST],", ")

 

View solution in original post

7 REPLIES 7
sheap069
Helper III
Helper III

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],", ")

 

Vera_33
Resident Rockstar
Resident Rockstar

Hi @sheap069 

 

You want a Table visual to display it? So it is a measure

 

Vera_33_0-1614931749099.png

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!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.