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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
danielc
Regular Visitor

Extracting min and max occurrences from text column as a measure

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.

 

 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

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

Card Measures.png

 

Hope the helps! Smiley Happy

View solution in original post

6 REPLIES 6
Phil_Seamark
Employee
Employee

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

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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
Community Champion
Community Champion

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

Card Measures.png

 

Hope the helps! Smiley Happy

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
Community Champion
Community Champion

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

Card Measures2.png

 

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"

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.