Forum Discussion

dlagos's avatar
dlagos
Regular Visitor
2 years ago
Solved

Sum a total in var

I have this dax, how to sum because the next dax is not working. how to fix.

VAR Dia = SELECTEDVALUE('Maestro Libro Diario Balance'[Accounting Date]) - SELECTEDVALUE('Maestro Libro Diario Balance'[Batch Created Date])
VAR RSC = IF(AND(Dia < 20, Dia > -20), 1, 0)
VAR TablaCondicional =
    ADDCOLUMNS(
        FILTER(
            ALL('Maestro Libro Diario Balance'),
            AND(
                ('Maestro Libro Diario Balance'[Accounting Date] - 'Maestro Libro Diario Balance'[Batch Created Date]) < 20,
                ('Maestro Libro Diario Balance'[Accounting Date] - 'Maestro Libro Diario Balance'[Batch Created Date]) > -20
            )
        ),
        "ValorCondicionado", RSC
    )
VAR TOTAL = SUMX(TablaCondicional, [ValorCondicionado])
RETURN
    TOTAL
Tutorial Requests
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    I am not sure how your semantic model looks like, but try something like below if it suits your requirement.

     

    expected result measure: =
VAR TablaCondicional =
    ADDCOLUMNS (
        FILTER (
            ALL ( 'Maestro Libro Diario Balance' ),
            AND (
                ( 'Maestro Libro Diario Balance'[Accounting Date] - 'Maestro Libro Diario Balance'[Batch Created Date] ) < 20,
                ( 'Maestro Libro Diario Balance'[Accounting Date] - 'Maestro Libro Diario Balance'[Batch Created Date] ) > -20
            )
        ),
        "ValorCondicionado",
            CALCULATE (
                IF (
                    AND (
                        SELECTEDVALUE ( 'Maestro Libro Diario Balance'[Accounting Date] )
                            - SELECTEDVALUE ( 'Maestro Libro Diario Balance'[Batch Created Date] ) < 20,
                        SELECTEDVALUE ( 'Maestro Libro Diario Balance'[Accounting Date] )
                            - SELECTEDVALUE ( 'Maestro Libro Diario Balance'[Batch Created Date] ) > -20
                    ),
                    1,
                    0
                )
            )
    )
VAR TOTAL =
    SUMX ( TablaCondicional, [ValorCondicionado] )
RETURN
    TOTAL

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    I am not sure how your semantic model looks like, but try something like below if it suits your requirement.

     

    expected result measure: =
VAR TablaCondicional =
    ADDCOLUMNS (
        FILTER (
            ALL ( 'Maestro Libro Diario Balance' ),
            AND (
                ( 'Maestro Libro Diario Balance'[Accounting Date] - 'Maestro Libro Diario Balance'[Batch Created Date] ) < 20,
                ( 'Maestro Libro Diario Balance'[Accounting Date] - 'Maestro Libro Diario Balance'[Batch Created Date] ) > -20
            )
        ),
        "ValorCondicionado",
            CALCULATE (
                IF (
                    AND (
                        SELECTEDVALUE ( 'Maestro Libro Diario Balance'[Accounting Date] )
                            - SELECTEDVALUE ( 'Maestro Libro Diario Balance'[Batch Created Date] ) < 20,
                        SELECTEDVALUE ( 'Maestro Libro Diario Balance'[Accounting Date] )
                            - SELECTEDVALUE ( 'Maestro Libro Diario Balance'[Batch Created Date] ) > -20
                    ),
                    1,
                    0
                )
            )
    )
VAR TOTAL =
    SUMX ( TablaCondicional, [ValorCondicionado] )
RETURN
    TOTAL
  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi dlagos 

     

    It looks as though the RSC and Dia variables are not iterated in row context but calculated based on the filter context, and the additional column 'ValorCondicional' is always equal to the RSC variable and won't vary across the different rows. Also, the Total variable is attempting to sum over 'TablaCondicional' but because of the 'ValorCondicionado' being the same for every row, it is essentially multiplying the same value by the number of rows in 'TablaCondicional.

     

    Try the below and hopefully this will help.

     

    VAR TablaCondicional =
    ADDCOLUMNS (
        FILTER (
            ALL ( 'Maestro Libro Diario Balance' ) ,
            AND (
                ( 'Maestro Libro Diario Balance'[Accounting Date] - 'Maestro Libro Diario Balance'[Batch Created Date] ) < 20 ,
                ( 'Maestro Libro Diario Balance'[Accounting Date] - 'Maestro Libro Diario Balance'[Batch Created Date] ) > -20
            )
        ) ,
        "ValorCondicionado" , IF ( AND ( ( 'Maestro Libro Diario Balance'[Accounting Date] - 'Maestro Libro Diario Balance'[Batch Created Date] ) < 20 , ( 'Maestro Libro Diario Balance'[Accounting Date] - 'Maestro Libro Diario Balance'[Batch Created Date] ) > -20 ) , 1 , 0 )
    )
VAR TOTAL = SUMX ( TablaCondicional , [ValorCondicionado] )

RETURN

    TOTAL

     

    Hopefully this helps (or at least the logic of it).

     

    Theo 🙂