Power BI: find the maximum value of unique numbers

There are 3 ways I know how to search for the higher value for unique numbers. For the first one, I will use the “group by” function from the power query but before to start, take note that this method may modify the structure of the table. Let´s say that I have this table:

power bi

“inc01” and “inc04” are duplicate and one of percentage is higher than the other so to remove the lower one:

  • Click on “transform data -> transform data”
    power bi
  • Select the duplicate column, in my case, it is “number”
  • Click on “Home -> group by”
    power bi

From here, there are 2 options, the left one will focus on “number” and “percentage” columns and for the other columns, it will group them in one single column. The right one will focus on all columns:

Left option Right option
power bi power bi
power bi power bi
Final result
power bi power bi

NOTE: for the minimum value, select Min instead of Max

On the left option, only “number” and “percentage” columns are showing and the other ones have disappear (in my case, the “maxduplicate” column). On the right option, to keep all columns, I need to add an aggregation for each column (in my case, just need to click once).

The second method is to create a new table from the main table so click on “home -> new table”:

power bi

Put this formula:

SUMMARIZE('table','table'[argument1],"value2",MAX('table'[argument2]),"value3",MAX('table'[argument3]))

power bi

NOTE:

  • Replace “table”, “argument” and “value” by yours
  • For the minimum value, replace MAX by MIN
power bi

The last method is to use only the main table so I will add a new column:

power bi

With this formula:

CALCULATE(MAX('table'[argument1]),ALLEXCEPT('table','table'[argument2]))

power bi

NOTE: to add another criteria, in ALLEXCEPT, just add another argument. For instance:

ALLEXCEPT('table','table'[argument2],'table'[argument3])

If I want to remove the lower value, I will use this one:

IF(CALCULATE(MAX('table'[argument1]),ALLEXCEPT('table','table'[argument2]))='table'[argument1],
'table'[argument1],BLANK())

power bi
power bi

Now, let´s do some visuals with the last method because for the first and second one, there is no need for some extra explanation. On the left, using the default columns and on the right using the new ones but first, I will have to create a measure with this formula:

CALCULATE(COUNT('table'[argument1]),NOT('table'[argument2])=BLANK())

power bi
Left visual Left build Right visual Right build
power bi power bi
power bi
power bi power bi
power bi

This article Power BI: calculate the (high) value of unique numbers may interest you.

Interesting Topics