topic Re: Understanding DAX in DAX Commands and Tips

Understanding DAX

BHAVIKCHAWLA — Sat, 23 Mar 2024 15:35:06 GMT

I am little new to Power BI and am finding little difficulty in understanding some concepts.
I was working - Highlighting max bars.
I am applied this logic -

Maximum Value =

Var MaxVal = MAXX(ALLEXCEPT(table,sum(column))

VAR Color = IF(sum(column) = MaxVal, "Dark Blue" , "Grey" )

RETURN Color
This gave me incorrect - actually all were dark blue.
Whereas if I create another measure total = sum(column) and replace like below

Maximum Value =

Var MaxVal = MAXX(ALLEXCEPT(table,total)

VAR Color = IF(total= MaxVal, "Dark Blue" , "Grey" )

RETURN Color
This gave me correct.
Can someone please help me in understanding concept. If any video is there related to this concept, please share

Re: Understanding DAX

Jihwan_Kim — Sun, 24 Mar 2024 07:24:23 GMT

Hi,

I am not sure how your semantic model looks like, but please try something like below.

I think it needs context transition in the measure.

Maximum Value = VAR MaxVal = MAXX ( ALL ( 'table' ), CALCULATE ( SUM ( 'table'[column] ) ) ) //need context transition and CALCULATE function enables context transition VAR Color = IF ( SUM ( 'table'[column] ) = MaxVal, "Dark Blue", "Grey" ) RETURN Color

Re: Understanding DAX

Anonymous — Mon, 25 Mar 2024 02:37:39 GMT

Hi BHAVIKCHAWLA ,

Thanks Jihwan_Kim for sharing, I have an addition here:

Allexcept() function:
1. Removes all context filters in the table except filters that have been applied to the specified columns.

2. Functions for grouping

Here are other function recommendations you might consider using the all() and allselected() functions:

All():

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

ALL function (DAX) - DAX | Microsoft Learn

Allselected():

Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

ALLSELECTED function (DAX) - DAX | Microsoft Learn

If you want to find the maximum value of each grouping after removing all filters, you can try the following dax:

Measure = MAXX(FILTER(ALL('Table'),'Table'[column]=MAX('Table'[column])),[Value])

If you want to find the maximum value of each grouping as Visual changes, you can try the following dax:

Group_Allselect = MAXX(FILTER(ALLSELECTED('Table'),'Table'[column]=MAX('Table'[column])),[Value])

If you want to find the maximum value for the whole table after removing all filters, you can try the following dax:

Max_All = MAXX(ALL('Table'),'Table'[Value])

If you want to find the maximum value for the whole table as Visual changes, you can try the following dax:

Max_Allselected = MAXX(ALLSELECTED('Table'),'Table'[Value])

This is the data from the original table:

This is the result of measure:

Best Regards,

Liu Yang

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