Forum Discussion

TomsNg's avatar
TomsNg
Helper I
2 years ago
Solved

How this DAX works

Hi All,

 

I am given a task to categorize item after the amount is summed. I got a solution provided by one of super users 'Jihwan_Kim' from this community. However, I tried but I can't realize how the DAX works. Can someone help to explain this DAX. 

 

 

*** This is the DAX ***

Sum by Category =

SUMX (
    FILTER (
        VALUES ( Data[Id] ),
        COUNTROWS (
            FILTER (
                Categories,
                CALCULATE ( SUM ( Data[Amount] ) ) >= Categories[Min]
                    && CALCULATE ( SUM ( Data[Amount] ) ) < Categories[Max]
            )
        ) > 0
    ),
    CALCULATE ( SUM ( Data[Amount] ) )
)
 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  TomsNg ,

    You can understand the meaning of the following dax formula in two parts:

    1. It filter the Ids which the sum of Amount is between Categories[Min] and Categories[Max]

      FILTER (
        VALUES ( Data[Id] ),
        COUNTROWS (
            FILTER (
                Categories,
                CALCULATE ( SUM ( Data[Amount] ) ) >= Categories[Min]
                    && CALCULATE ( SUM ( Data[Amount] ) ) < Categories[Max]
            )
        ) > 0
    )

    2. This is calculating the sum of the Amount for each Id that meets the conditions specified in the filter. The SUMX function is an iterator that calculates the sum row by row.

    SUMX (
XXX,
 CALCULATE ( SUM ( Data[Amount] ) )
)

    You can refer the following official documentations to get understanding of the involved functions:

    FILTER function (DAX) - DAX | Microsoft Learn

    VALUES function (DAX) - DAX | Microsoft Learn

    COUNTX function (DAX) - DAX | Microsoft Learn

    SUMX function (DAX) - DAX | Microsoft Learn

     

    In addition, you can create two measures as below to achieve the same requirement. You can find the details in the attachment.

    Measure = 
VAR _tab =
    SUMMARIZE ( 'Data', 'Data'[Id], "@amount", SUM ( 'Data'[Amount] ) )
RETURN
    SUMX (
        FILTER (
            _tab,
            [@amount] >= SELECTEDVALUE ( 'Categories'[Min] )
                && [@amount] < SELECTEDVALUE ( 'Categories'[Max] )
        ),
        [@amount]
    )
    Sum by category = SUMX(VALUES('Categories'[Category]),[Measure])

    Best Regards

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  TomsNg ,

    You can understand the meaning of the following dax formula in two parts:

    1. It filter the Ids which the sum of Amount is between Categories[Min] and Categories[Max]

      FILTER (
        VALUES ( Data[Id] ),
        COUNTROWS (
            FILTER (
                Categories,
                CALCULATE ( SUM ( Data[Amount] ) ) >= Categories[Min]
                    && CALCULATE ( SUM ( Data[Amount] ) ) < Categories[Max]
            )
        ) > 0
    )

    2. This is calculating the sum of the Amount for each Id that meets the conditions specified in the filter. The SUMX function is an iterator that calculates the sum row by row.

    SUMX (
XXX,
 CALCULATE ( SUM ( Data[Amount] ) )
)

    You can refer the following official documentations to get understanding of the involved functions:

    FILTER function (DAX) - DAX | Microsoft Learn

    VALUES function (DAX) - DAX | Microsoft Learn

    COUNTX function (DAX) - DAX | Microsoft Learn

    SUMX function (DAX) - DAX | Microsoft Learn

     

    In addition, you can create two measures as below to achieve the same requirement. You can find the details in the attachment.

    Measure = 
VAR _tab =
    SUMMARIZE ( 'Data', 'Data'[Id], "@amount", SUM ( 'Data'[Amount] ) )
RETURN
    SUMX (
        FILTER (
            _tab,
            [@amount] >= SELECTEDVALUE ( 'Categories'[Min] )
                && [@amount] < SELECTEDVALUE ( 'Categories'[Max] )
        ),
        [@amount]
    )
    Sum by category = SUMX(VALUES('Categories'[Category]),[Measure])

    Best Regards