Extracting min and max occurrences from text column as a measure

[danielc]

Hello Community.

Was wondering how I could extract the minimum and maximum occurrences from a text based column. For example: 

[Column 1 (Country)]

Australia

Australia

Australia

Australia

France

France

India

India

China

I want to create a measure that returns Australia - 4 for max. and China - 1 for min.

Thank you.

[Phil_Seamark]

Hi @danielc,

If you want them in separate measures, here is one approach.  Not entirely sure if this is what you are after though.....

Max Country = SELECTCOLUMNS(
			TOPN(
				1,
				SUMMARIZECOLUMNS(
					'Table3'[Country] , 
					"Count" , 
					COUNTROWS('Table3')
					),
				[Count],
				ASC) 
			, 
			"Min Country" , 
			[Country] & " - " & [Count]
			)

and for MAX

Min Country = SELECTCOLUMNS(
			TOPN(
				1,
				SUMMARIZECOLUMNS(
					'Table3'[Country] , 
					"Count" , 
					COUNTROWS('Table3')
					),
				[Count],
				DESC) 
			, 
			"Min Country" , 
			[Country] & " - " & [Count]
			)

[danielc]

Thank you for your response Phil, I tried this code however it returns the count of the first category by alphabetical order.

eg. there are black,brown,blonde colours in my column, there are more counts of brown but the measure returns black. The measure also did the same for another column.

Using this.

MAX EYE HAIR COLOUR = SELECTCOLUMNS(
							TOPN(
								1,
								SUMMARIZECOLUMNS(
									'Missing Children'[HAIR COLOUR],
									"COUNT",
									COUNTROWS('Missing Children')
									),
								[HAIR COLOUR],
								1)
							,
							"MAX COUNTRY",
							[HAIR COLOUR] & " - " & [COUNT]
							)

[Sean · Accepted solution]

I would not recommend using TOPN for this for the cases where you have ties!

Here's the Data sample I used expanding on your original dataset...

Country

Australia

Australia

Australia

Australia

France

France

India

India

China

New Zealand

New Zealand

New Zealand

New Zealand

Japan

To handles the ties use these - ALL Measures!

Total Count = COUNTA ( 'Table'[Country] )

Top Country Rank = RANKX ( ALLSELECTED ( 'Table'[Country] ), [Total Count],, DESC )

Top Country Name(s) =
CONCATENATEX (
    FILTER ( VALUES ( 'Table'[Country] ), [Top Country Rank] = 1 ),
    'Table'[Country], ", ", 'Table'[Country], ASC
)

Number of Top Countries =
COUNTROWS ( FILTER ( VALUES ( 'Table'[Country] ), [Top Country Rank] = 1 ) )

Top Country Total Count (per Country!) =
DIVIDE (
    CALCULATE (
        [Total Count],
        TOPN ( 1, ALLSELECTED ( 'Table'[Country] ), [Top Country Rank], ASC )
    ),
    [Number of Top Countries],
    0
)

Top Card Measure =
[Top Country Name(s)] & " - "
    & [Top Country Total Count (per Country!)]
    & IF ( [Number of Top Countries] > 1, " each", "" )
    & " for MAX"

To get the Bottom Values change the RANK the 2nd Measure above to ASC instead of DESC ...

And the results...

Hope the helps!

[danielc]

Sean, thank you for the helpful response. Does this mean 11 measures in total would be required to have the both the bottom and top country(s) cards on the dashboard? It seems like a lot of work to display one columns max and min occurrences, in the example I was working on I had to do this for 3-4 columns, so 44 measures would be needed...

[Sean]

You can do this with less but you'll have less customization options...

So just use the Top 2 Measures + create 1 for the Bottom Rank (that would be 3)

Plus 1 called Flag

Flag = IF ( OR ([Top Country Rank]=1,[Bottom Country Rank]=1), 1, 0 )

Create a Table and Place Flag in the Visual Level Filters and select is 1 apply filter

[Phil_Seamark]

Hi @danielc,

Here is an approach in Power query that returns a 2 row table showing the MAX and MIN countries, including counts.

This is more for me playing around in M.  The main code is highlighted in M.  The other rows are just setup and renaming.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwtLilKzMlMVIrVIYXnVpSYl5yKzvTMS4HKI1jOGZl5QFYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Country = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Country"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Ascending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
    #"Added Custom" = Table.AddColumn(#"Kept First Rows", "Custom", each "Min"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Custom", "Country", "Count"}),
    #"Kept Last Rows" = Table.LastN(#"Sorted Rows", 1),
    #"Added Custom1" = Table.AddColumn(#"Kept Last Rows", "Custom", each "Max"),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom1",{"Custom", "Country", "Count"}),
    #"Appended Query" = Table.Combine({#"Reordered Columns1", #"Reordered Columns"}),
    #"Renamed Columns" = Table.RenameColumns(#"Appended Query",{{"Custom", "Type"}})
in
    #"Renamed Columns"