Forum Discussion

salafoo's avatar
salafoo
Helper I
5 years ago
Solved

Two filters variables calculation error

Hello All,   I hopu you are doing well?   I am writing to get a help with a code, I was trying to create a caluculated measure with multiple filters using variables, however, the formula kept gen...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi salafoo,

    I try to simplify your formula and merge these conditions, you can try to use the following expressions if it works on your side.

    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 )
    )

    Regards,
    Xiaoxin Sheng

aj1973's avatar
aj1973
Community Champion
5 years ago

Hi salafoo 

If the formula is not returning an error it means that your DAX is correct. However we can't detect where the zero is coming from without sharing with us more details or a Sample of Pbix file.

I would recommend that after the RETURN of your formula check what every variable is returning as a result, also check those SUMX one by one until you determin where is the ZERO coming from.

 

Good luck

 

salafoo's avatar
salafoo
Helper I
5 years ago

Thanks Amine for you resopnse, The propleme when I create the formula with no variables it works, but when code it like the above it does generate zeros with no code error. Below is the old formula:

 

 

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"
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi salafoo,

    I try to simplify your formula and merge these conditions, you can try to use the following expressions if it works on your side.

    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 )
    )

    Regards,
    Xiaoxin Sheng