Forum Discussion

DmitryAD7's avatar
DmitryAD7
Helper I
2 years ago
Solved

IF and SELECTEDVALUE Condition

I need a measure that will calculate the following condition: if only two atributes D1 and D2 are selected in the slicer, the D1 value (100) should be multiplied (1.25) and then summ with the D2 value (100). Expected value result - 225.

In all other cases, only the Total Value result (for 1 - 100, for 2 - 200, for 3 - 300, for 4 - 400, for all - 500) should be displayed.

 

Current DAX is not working:

Sales with Condition =
VAR _select =
    SELECTEDVALUE ( 'Dataset'[D] )
VAR _d1_value =
    CALCULATE ( [Total Value], FILTER ( 'Dataset', 'Dataset'[D] = "D1" ) ) * [Multiplicator]
VAR _d2_value =
    CALCULATE ( [Total Value], FILTER ( 'Dataset', 'Dataset'[D] = "D2" ) )
VAR _d3_value =
    CALCULATE ( [Total Value], FILTER ( 'Dataset', 'Dataset'[D] = "D3" ) )
VAR _d4_value =
    CALCULATE ( [Total Value], FILTER ( 'Dataset', 'Dataset'[D] = "D4" ) )
VAR _d5_value =
    CALCULATE ( [Total Value], FILTER ( 'Dataset', 'Dataset'[D] = "D5" ) )
VAR _total_d_value = _d1_value + _d2_value + _d3_value + _d4_value + _d5_value
VAR Result =
    IF ( AND ( _select = "D1", _select = "D2" ), _total_d_value, [Total Value] )
RETURN
    Result
 
Thanks for your help.
  • talespin's avatar
    talespin
    2 years ago

    hi DmitryAD7 

     

    Please try this measure 

     

    Sales with Condition =
    VAR _CountCheck = COUNT('Dataset'[D])
    VAR _val = CALCULATE ( [Total Value], REMOVEFILTERS('Dataset'[D]), 'Dataset'[D] = "D1" )
    VAR _d1_value = _val*1.25
    VAR _d2_value =
        CALCULATE ( [Total Value], REMOVEFILTERS('Dataset'[D]), 'Dataset'[D] = "D2" )
    VAR _default_value =
        CALCULATE ( [Total Value], REMOVEFILTERS('Dataset'[D]), VALUES('Dataset'[D]) )
    VAR Result =
        IF ( CONTAINSROW(VALUES('Dataset'[D]), "D1")  && CONTAINSROW(VALUES('Dataset'[D]), "D2") && _CountCheck = 2,
            _d1_value + _d2_value,
            _default_value
        )
    RETURN
        Result
     

     

6 Replies

  • talespin's avatar
    talespin
    Solution Sage
    2 years ago

    hi DmitryAD7 

     

    Please try this measure 

     

    Sales with Condition =
    VAR _CountCheck = COUNT('Dataset'[D])
    VAR _val = CALCULATE ( [Total Value], REMOVEFILTERS('Dataset'[D]), 'Dataset'[D] = "D1" )
    VAR _d1_value = _val*1.25
    VAR _d2_value =
        CALCULATE ( [Total Value], REMOVEFILTERS('Dataset'[D]), 'Dataset'[D] = "D2" )
    VAR _default_value =
        CALCULATE ( [Total Value], REMOVEFILTERS('Dataset'[D]), VALUES('Dataset'[D]) )
    VAR Result =
        IF ( CONTAINSROW(VALUES('Dataset'[D]), "D1")  && CONTAINSROW(VALUES('Dataset'[D]), "D2") && _CountCheck = 2,
            _d1_value + _d2_value,
            _default_value
        )
    RETURN
        Result
     

     

  • nareshr89's avatar
    nareshr89
    Helper II
    2 years ago

    Try this

     

     

    Sales with Condition =
    VAR _select = SELECTEDVALUE ( 'Dataset'[D] )

    VAR _d1_value =
    CALCULATE ( [Total Value], FILTER ( 'Dataset', 'Dataset'[D] = "D1" ) ) *1.25
    VAR _d2_value =
    CALCULATE ( [Total Value], FILTER ( 'Dataset', 'Dataset'[D] = "_D2" ) )
    VAR _defualt_value =
    CALCULATE ( [Total Value], FILTER ( 'Dataset', 'Dataset'[D] = "_select" ) )

    VAR Result =
    IF ( _select = "D1" && _select = "D2", CALCULATE(_d1_value+_d2_value),_defualt_value)

     

    If it doesn't work, share a sample PBIX file.

      • govindarajan_d's avatar
        govindarajan_d
        Super User
        2 years ago

        Hi DmitryAD7 ,

        Try this:

        I have used CONCATENATEX to build a string that contains the values selected in slicer and check if it is equal to "D1, D2"

         

        VAR _select =
    CONCATENATEX(
        VALUES('Dataset'[D]),
        'Dataset'[D],
        ", "
    )
VAR _d1_value =
    CALCULATE ( [Total Value], FILTER ( 'Dataset', 'Dataset'[D] = "D1" ) ) * 1.25
VAR _d2_value =
    CALCULATE ( [Total Value], FILTER ( 'Dataset', 'Dataset'[D] = "D2" ) )
VAR _default_value =
    CALCULATE ( [Total Value], FILTER ( 'Dataset', 'Dataset'[D] = "_select" ) )
VAR Result =
    IF (
        CONTAINSSTRING(_select,"D1, D2"),
        CALCULATE ( _d1_value + _d2_value ),
        _default_value
    )
RETURN
    Result