Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

SUM IF Multiple conditions

Hi, 

 

I need to created the following SIM IF logic, but can't get the correct syntax for it, can anyone help me with this, please?:

 

sum
if comps[spend] <= 300000 then dim_apps[value_300] else 
if comps[spend] > 300000 and <=400000 then dim_apps[value_400] else 

if comps[spend] > 400000 and <=500000 then dim_apps[value_400] else 
if comps[spend] > 500000 then dim_apps[value_500] 

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Anonymous ,

     

    If you want this for measure, please use code like this and Id field should be placed in table vsiual first:

    IF =
VAR _spend =
    SUM ( comps[spend] )
RETURN
    IF (
        _spend <= 300000,
        SUM ( dim_apps[value_300] ),
        IF (
            _spend > 300000
                && _spend <= 400000,
            SUM ( dim_apps[value_400] ),
            IF (
                _spend > 400000
                    && _spend <= 500000,
                SUM ( dim_apps[value_400] ),
                SUM ( dim_apps[value_500] )
            )
        )
    )

     

    If calculate column, you need use related() to get the field in dim_apps table:

    a =
VAR _spend = comps[spend]
RETURN
    IF (
        _spend <= 300000,
        RELATED ( dim_apps[value_300] ),
        IF (
            _spend > 300000
                && _spend <= 400000,
            RELATED ( dim_apps[value_400] ),
            IF (
                _spend > 400000
                    && _spend <= 500000,
                RELATED ( dim_apps[value_400] ),
                RELATED ( dim_apps[value_500] )
            )
        )
    )

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • GunnerJ's avatar
    GunnerJ
    Icon for Post Patron rankPost Patron
    4 years ago

    **I made the assumption that dim_apps was the name of the column. also the value for the middle two conditions is the same just incase it wasn't supposed to be like that. Let me know if this works. **

    dim_apps = if(comps[spend] <= 300000, 300, if(comps(spend > 300000 && <= 400000, 400, if(comps[spend] > 400000 && <= 500000, 400, 500))))

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi,

      The difficulty that I am having is that the I am trying to create this calculation using values across 2 tables (comps and dim_apps). 

       

      So if I start with having the value less than or equal to 300000, then I need to pull the value stored in the dim_apps table looking at the value within the field [value_300] etc. 

       

      The 2 tables comps and dim_apps are joined together using their unique ID. 

       

       

      spend = if(comps[spend] <= 300000, dim_apps[value_300]

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous ,

         

        If you want this for measure, please use code like this and Id field should be placed in table vsiual first:

        IF =
VAR _spend =
    SUM ( comps[spend] )
RETURN
    IF (
        _spend <= 300000,
        SUM ( dim_apps[value_300] ),
        IF (
            _spend > 300000
                && _spend <= 400000,
            SUM ( dim_apps[value_400] ),
            IF (
                _spend > 400000
                    && _spend <= 500000,
                SUM ( dim_apps[value_400] ),
                SUM ( dim_apps[value_500] )
            )
        )
    )

         

        If calculate column, you need use related() to get the field in dim_apps table:

        a =
VAR _spend = comps[spend]
RETURN
    IF (
        _spend <= 300000,
        RELATED ( dim_apps[value_300] ),
        IF (
            _spend > 300000
                && _spend <= 400000,
            RELATED ( dim_apps[value_400] ),
            IF (
                _spend > 400000
                    && _spend <= 500000,
                RELATED ( dim_apps[value_400] ),
                RELATED ( dim_apps[value_500] )
            )
        )
    )

         

        Best Regards

        Community Support Team _ chenwu zhu

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • NickolajJessen's avatar
    NickolajJessen
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Thomas!

    First of, you'll want to stay away from these nested if's and in stead use the function SWITCH for readability and performance.

    Secondly, you'll notice that there is actually no reason to have a "Between" logic built in, as the IF and switch function will exit when it hits the first statement to return TRUE