DistinctCount with Max Users in Particular field

[erikajain02]

Hi Team,

I am trying to find out the State with max users for each  Operator.

Desired Output : Airtel : Maharastra

                          BSNL :  Uttar Pradesh

                          Vodafone : Uttar Pradesh

Sample Data : 

Operator	In Out Travelling	Network Type	Rating	Call Drop Category	Latitude	Longitude	State Name
RJio	Travelling	4G	2	Poor Voice Quality	28.59359	77.16427589	NCT
RJio	Travelling	4G	5	Satisfactory	28.59363	77.1642034	NCT
RJio	Indoor	4G	5	Satisfactory	25.62589	85.09438546	Bihar
RJio	Indoor	4G	5	Satisfactory	25.62589	85.09438546	Bihar
RJio	Outdoor	4G	2	Poor Voice Quality	21.37492	81.66704286	Chhattisgarh
RJio	Outdoor	4G	1	Call Dropped	28.6076	77.02321833	NCT
RJio	Outdoor	4G	4	Satisfactory	12.36292	76.6441789	Karnataka
RJio	Outdoor	4G	5	Satisfactory	28.12087	77.3307241	Haryana
RJio	Outdoor	4G	1	Poor Voice Quality	17.25423	82.53787922	Andhra Pradesh
RJio	Travelling	4G	1	Poor Voice Quality	17.45379	78.56600319	Telangana
RJio	Indoor	4G	1	Call Dropped	28.6076	77.02321833	NCT
BSNL	Indoor	Unknown	2	Poor Voice Quality	26.84915	80.956541	Uttar Pradesh
BSNL	Indoor	Unknown	2	Poor Voice Quality	26.84915	80.956541	Uttar Pradesh
BSNL	Indoor	3G	3	Satisfactory	28.96588	77.7362908	Uttar Pradesh
Airtel	Outdoor	4G	4	Satisfactory	11.2422	77.2331508	Tamil Nadu
Airtel	Outdoor	4G	4	Satisfactory	11.2422	77.2331508	Tamil Nadu
Airtel	Indoor	3G	1	Call Dropped	13.01054	77.703192	Karnataka
RJio	Travelling	4G	2	Poor Voice Quality	28.6635	77.26747369	NCT
Vodafone	Indoor	4G	5	Satisfactory	28.62235	77.3671665	Uttar Pradesh
Vodafone	Indoor	Unknown	5	Satisfactory	28.62235	77.3671665	Uttar Pradesh
Vodafone	Indoor	Unknown	5	Satisfactory	28.62235	77.3671665	Uttar Pradesh
Vodafone	Indoor	Unknown	5	Satisfactory	28.57444	77.3537819	Uttar Pradesh
Vodafone	Indoor	4G	5	Satisfactory	28.61904	77.4252032	Uttar Pradesh
RJio	Outdoor	4G	1	Poor Voice Quality	20.82244	85.28371866	Odisha
Airtel	Indoor	Unknown	2	Poor Voice Quality	19.40829	72.8387402	Maharashtra
Airtel	Indoor	2G	2	Poor Voice Quality	19.40829	72.8387402	Maharashtra
BSNL	Outdoor	4G	4	Satisfactory	10.48786	76.96689751	Tamil Nadu
Airtel	Indoor	Unknown	2	Poor Voice Quality	19.28426	72.94790689	Maharashtra
Airtel	Indoor	Unknown	2	Poor Voice Quality	19.28426	72.94790689	Maharashtra
Airtel	Indoor	Unknown	2	Poor Voice Quality	19.40829	72.8387402	Maharashtra
Airtel	Indoor	2G	2	Poor Voice Quality	19.40829	72.8387402	Maharashtra
Airtel	Travelling	3G	1	Call Dropped	19.11229	72.91005411	Maharashtra
Airtel	Outdoor	3G	2	Poor Voice Quality	19.28426	72.94790689	Maharashtra

[v-yulgu-msft]

Hi @erikajain02,

New a calculated table:

Result output =
SUMMARIZE (
    FILTER (
        ADDCOLUMNS (
            Table,
            "Users", CALCULATE (
                COUNT ( Table[State Name] ),
                ALLEXCEPT ( Table, Table[Operator], Table[State Name] )
            ),
            "Max Users", CALCULATE (
                MAXX (
                    SUMMARIZE (
                        Table,
                        Table[Operator],
                        Table[State Name],
                        "Use", COUNT ( Table[State Name] )
                    ),
                    [Use]
                ),
                ALLEXCEPT ( Table, Table[Operator] )
            )
        ),
        [Users] = [Max Users]
    ),
    Table[Operator],
    Table[State Name]
)

Best regards,

Yuliana Gu

[erikajain02]

@v-yulgu-msft

Can u share the application Please?

[erikajain02]

I guess I do not have permissions to upload the file.

Try This Link : https://app.powerbi.com/groups/me/reports/d6cf5f96-bcda-4a29-b4c0-47858ea3bda8?ctid=a45fe71a-f480-4e...

[erikajain02]

Try here :

https://www.dropbox.com/home/Power%20BI?preview=Call+Voice+Quality.pbix

[erikajain02]

Hey @v-yulgu-msft,

It is giving me syntax error.

[erikajain02]

Hey @v-yulgu-msft,

Is there a shorter way to do this ?