Forum Discussion

BHAVIKCHAWLA's avatar
BHAVIKCHAWLA
New Member
2 years ago
Solved

Understanding DAX

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
DAX
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.