Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

how to create a calculation that switch values based on selected value from slicer use DAX?

Hi, recently I met a requirement that two columns should be switched based on selected monthid value，

    the reslut has the value from col1 when monthid < selected monthid and has the value from col2 when monthid >= selected monthid, and I have no idea how to create result calculation value

here is a example:

do you have any solutions？Thank you

Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    I suggest having a disconnected Slicer Table for selecting a month number.

     

     

    Result measure: = 
VAR _currentmonthrow =
    MAX ( Data[month_id] )
VAR _slicerselect =
    MAX ( 'Month ID slicer table'[month_id] )
VAR _colone =
    SUM ( Data[col1] )
VAR _coltwo =
    SUM ( Data[col2] )
RETURN
    IF (
        HASONEVALUE ( Data[month_id] ),
        SWITCH (
            TRUE (),
            _slicerselect > _currentmonthrow, _colone,
            _slicerselect <= _currentmonthrow, _coltwo
        )
    )

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    I suggest having a disconnected Slicer Table for selecting a month number.

     

     

    Result measure: = 
VAR _currentmonthrow =
    MAX ( Data[month_id] )
VAR _slicerselect =
    MAX ( 'Month ID slicer table'[month_id] )
VAR _colone =
    SUM ( Data[col1] )
VAR _coltwo =
    SUM ( Data[col2] )
RETURN
    IF (
        HASONEVALUE ( Data[month_id] ),
        SWITCH (
            TRUE (),
            _slicerselect > _currentmonthrow, _colone,
            _slicerselect <= _currentmonthrow, _coltwo
        )
    )