topic Re: Measure that returns the Max value by multiple catagories in DAX Commands and Tips

Measure that returns the Max value by multiple catagories

hello_ — Mon, 15 Jan 2024 06:24:16 GMT

Hello Authors I am in a bite of bind please help I want to make a DAX Measure that returns the top or MAX value only by that categories please Guide me

Thank You

Re: Measure that returns the Max value by multiple catagories

123abc — Tue, 16 Jan 2024 05:19:02 GMT

Certainly! In Power BI, you can create a DAX measure to return the maximum value by multiple categories. Assuming you have a table with the relevant data, and you want to find the maximum value based on certain categories, you can use the following DAX formula as an example:

MaxValueByCategory =
CALCULATE(
MAX('YourTableName'[YourNumericColumn]),
ALLEXCEPT('YourTableName', 'CategoryColumn1', 'CategoryColumn2', ...)
)

Here, replace 'YourTableName' with the actual name of your table, 'YourNumericColumn' with the column containing the values you want to find the maximum of, and 'CategoryColumn1', 'CategoryColumn2', etc., with the columns representing your categories.

The ALLEXCEPT function removes all filters from the table except for the specified columns, ensuring that the maximum is calculated within the selected categories.

For example, if you have a Sales table with columns 'Product', 'Region', and 'SalesAmount', and you want to find the maximum sales amount by product and region, the formula would look like this:

MaxSalesByProductAndRegion =
CALCULATE(
MAX('Sales'[SalesAmount]),
ALLEXCEPT('Sales', 'Product', 'Region')
)

Adjust the column names based on your specific dataset and requirements. If you have more than two categories, include additional columns within the ALLEXCEPT function.

Re: Measure that returns the Max value by multiple catagories

Anonymous — Tue, 16 Jan 2024 09:09:49 GMT

Hi hello_ ,

Please follow these steps:

1.You can create a new metric and write the following expression.

Measure = var _max_cou = MAXX( FILTER( ALL('Table'), 'Table'[Category_1]=MAX('Table'[Category_1])&&'Table'[Category_2]=MAX('Table'[Category_2])), [Count_Category]) RETURN IF(MAX('Table'[Count_Category]) = _max_cou, _max_cou, BLANK() )

2.The results obtained are shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

^{Best Regards,}

^{Clara Gong}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

Re: Measure that returns the Max value by multiple catagories

hello_ — Thu, 18 Jan 2024 12:14:25 GMT

Thank You Sir for the solution it helps 🙏😊

Re: Measure that returns the Max value by multiple catagories

hello_ — Thu, 18 Jan 2024 12:16:20 GMT

Thank You very much