Two filters variables calculation error

[salafoo]

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 generating ZERO value. I assueme the issue is due to the filers. Can you please advise?

thanks

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)
    

[aj1973]

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]

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

[v-shex-msft · Accepted solution]

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]

Yes I see, you are using variables as filters inside the CALCULATE function.

You can't use variables at the filter level of the Calculate function;

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

Well why don't you keep it that way then?