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

 

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

4 Replies

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