topic Re: Find the max of a column with filters from other columns in DAX Commands and Tips

Find the max of a column with filters from other columns

stino123 — Mon, 24 Jan 2022 13:37:20 GMT

Hey,

I have a column in my shopify data that compiles price and discount price as line price. I want to calculate total discount, to do so based on the unique barcode return the max value (line price) for a given barcode. I will after subtract the difference of the new column and "line price" to calculate discounts. However my formula (below) will generate the row value rather than the max value for a given barcode. Added complication I need to exclude all blank values in column barcode.

Dataset

Column A: Line price

Column B: Barcode

New column C (objective) find max value for any given barcode

=CALCULATE(max(FACT_Orders[Line Price]),DISTINCT(FACT_Orders[Barcode]),FACT_Orders[Barcode]<> BLANK())

Could someone please help me out?

Thanks in advance!

Re: Find the max of a column with filters from other columns

ValtteriN — Mon, 24 Jan 2022 13:47:57 GMT

Hi,

You can achieve this with e.g. ALLEXCEPT:

=CALCULATE(max(FACT_Orders[Line Price]),ALLEXCEPT(FACT_Orders[Barcode]))
The idea here is to remove all the other filters except for the barcode and get the max value.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: Find the max of a column with filters from other columns

stino123 — Mon, 24 Jan 2022 14:02:05 GMT

Hey Valtteri,

This helped allot, for those products that don't have barcodes how do you exclude it from the equation? since now the few products that have "blank" cells within the column "barcode" all have the same price.

Re: Find the max of a column with filters from other columns

stino123 — Mon, 24 Jan 2022 14:12:50 GMT

Figured it out:

Full price = IF(FACT_Orders[Barcode]<> BLANK(),

CALCULATE(max(FACT_Orders[Line Price]),

ALLEXCEPT(FACT_Orders,FACT_Orders[Barcode])),

FACT_Orders[Line Price])

Thanks again for the help 🙂