Measure that returns the Max value by multiple catagories

[hello_]

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 

[hello_]

Thank You very much 

[v-kaiyue-msft · Accepted solution]

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

[hello_]

Thank You Sir for the solution it helps 🙏😊

[123abc]

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.