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

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.