cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Error de cálculo de dos variables de filtros

Hola a todos,

¿Te va bien?

Estoy escribiendo para obtener una ayuda con un código, estaba tratando de crear una medida caluculada con múltiples filtros usando variables, sin embargo, la fórmula seguía generando valor ZERO. Me assueme el problema se debe a los archivadores. ¿Puede aconsejar, por favor?

gracias

Under process WS Grant = 
VAR 
    UnderProcessFilter = 
       FILTER('TWS Wage Subsidy','TWS Wage Subsidy'[Workflow Status (groups)] = "Under Process"
        && 'TWS Wage Subsidy'[Status] = "Active"
        && (
             RELATED('TWS Employee Application'[Workflow Status] ) <> "Sent Back to Portal"
                &&  RELATED( 'TWS Employee Application'[Workflow Status]) <> "Created - Not Submitted"
            )
        &&  RELATED('TWS Employee Application'[Monitoring Status])  <> "Flagged")
        

VAR
    Diploma        = FILTER('TWS Wage Subsidy','TWS Wage Subsidy'[Segment Reference (groups)] = "MoL Segment - Diploma")  

RETURN

CALCULATE (
    SUMX (
       'TWS Wage Subsidy',
        IF (
            'TWS Wage Subsidy'[Current Wage] * 12 * 0.7 > 8400,
            8400,
            'TWS Wage Subsidy'[Current Wage] * 12 * 0.7
        )
    )
        + SUMX (
            'TWS Wage Subsidy',
            IF (
                'TWS Wage Subsidy'[Current Wage] * 6 * 0.5 > 4200,
                4200,
                'TWS Wage Subsidy'[Current Wage] * 6 * 0.5
            )
        ), Diploma,UnderProcessFilter)
    

1 ACCEPTED SOLUTION

No @salafoo,

Trato de simplificar su fórmula y combinar estas condiciones, puede intentar utilizar las siguientes expresiones si funciona de su lado.

Under process WS Grant =
VAR UnderProcessFilter =
    CALCULATETABLE (
        'TWS Wage Subsidy',
        FILTER (
            ALLSELECTED ( 'TWS Wage Subsidy' ),
            [Segment Reference (groups)] = "MoL Segment - Diploma"
                && [Workflow Status (groups)] = "Under Process"
                && [Status] = "Active"
        ),
        FILTER (
            ALLSELECTED ( 'TWS Employee Application' ),
            NOT ( [Workflow Status] IN { "Sent Back to Portal", "Created - Not Submitted" } )
                && [Monitoring Status] <> "Flagged"
        )
    )
RETURN
    SUMX (
        UnderProcessFilter,
        MIN ( [Current Wage] * 12 * 0.7, 8400 )
            + MIN ( [Current Wage] * 6 * 0.5, 4200 )
    )

saludos
Xiaoxin Sheng

View solution in original post

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

No hay @salafoo

Si la fórmula no devuelve un error significa que su DAX es correcto. Sin embargo, no podemos detectar de dónde viene el cero sin compartir con nosotros más detalles o un archivo sample of Pbix.

Recomendaría que después de la DEVOLUCIÓN de su fórmula comprobar lo que cada variable está regresando como resultado, también comprobar esos SUMX uno por uno hasta que determine de dónde viene el CERO.

Buena suerte

Gracias Amine por resopnse, El propleme cuando creo la fórmula sin variables funciona, pero cuando se codifica como lo anterior genera ceros sin error de código. A continuación se muestra la fórmula antigua:

Under process WS Grant =
CALCULATE (
    SUMX (
        'TWS Wage Subsidy',
        IF (
            'TWS Wage Subsidy'[Current Wage] * 12 * 0.7 > 8400,
            8400,
            'TWS Wage Subsidy'[Current Wage] * 12 * 0.7
        )
    )
        + SUMX (
            'TWS Wage Subsidy',
            IF (
                'TWS Wage Subsidy'[Current Wage] * 6 * 0.5 > 4200,
                4200,
                'TWS Wage Subsidy'[Current Wage] * 6 * 0.5
            )
        ),
    FILTER (
        'TWS Wage Subsidy',
        'TWS Wage Subsidy'[Segment Reference (groups)] = "MoL Segment - Diploma"
    ),
    'TWS Wage Subsidy'[Workflow Status (groups)] = "Under Process",
    'TWS Wage Subsidy'[Status] = "Active",
    ( 'TWS Employee Application'[Workflow Status] <> "Sent Back to Portal"
        && 'TWS Employee Application'[Workflow Status] <> "Created - Not Submitted" ),
    'TWS Employee Application'[Monitoring Status] <> "Flagged"
)

No @salafoo,

Trato de simplificar su fórmula y combinar estas condiciones, puede intentar utilizar las siguientes expresiones si funciona de su lado.

Under process WS Grant =
VAR UnderProcessFilter =
    CALCULATETABLE (
        'TWS Wage Subsidy',
        FILTER (
            ALLSELECTED ( 'TWS Wage Subsidy' ),
            [Segment Reference (groups)] = "MoL Segment - Diploma"
                && [Workflow Status (groups)] = "Under Process"
                && [Status] = "Active"
        ),
        FILTER (
            ALLSELECTED ( 'TWS Employee Application' ),
            NOT ( [Workflow Status] IN { "Sent Back to Portal", "Created - Not Submitted" } )
                && [Monitoring Status] <> "Flagged"
        )
    )
RETURN
    SUMX (
        UnderProcessFilter,
        MIN ( [Current Wage] * 12 * 0.7, 8400 )
            + MIN ( [Current Wage] * 6 * 0.5, 4200 )
    )

saludos
Xiaoxin Sheng

View solution in original post

Sí veo, está utilizando variables como filtros dentro de la función CALCULATE.

No puede utilizar variables en el nivel de filtro de la función Calcular;

https://docs.microsoft.com/en-us/dax/calculate-function-dax

Entonces, ¿por qué no lo mantienes así?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors