Sum a total in var

[dlagos]

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

[TheoC]

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 🙂

[Jihwan_Kim · Accepted solution]

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