Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

2 slicers from same columns

Hi folks,   I'm facing problem that I am unable to solve by myself, I need you.   I have a table call_status like that   call_id status begin_date end_date 1 open 2021-06-25 13:05:22...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi Anonymous ,

    Base on before pbix file Iprovided, Try following steps:

    Step1，Use the following measure ,create two begin_date:

    begin_date1 =
CALCULATE (
    MAX ( 'Table'[begin_date] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[call_num] = MAX ( 'Table'[call_num] )
            && 'Table'[status] = SELECTEDVALUE ( Slicer1[status] )
    )
)
    begin_date2 =
CALCULATE (
    MAX ( 'Table'[begin_date] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[call_num] = MAX ( 'Table'[call_num] )
            && 'Table'[status] = SELECTEDVALUE ( 'Slicer 2'[status] )
    )
)

    Step 2， adjust before measure:

    SILCERchoosedatediff = 
VAR TIME1 =
    CALCULATE (
        MAX ( 'Table'[begin_date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[status] = SELECTEDVALUE ( Slicer1[status] )&&'Table'[call_num]=MAX('Table'[call_num]) )
    )
VAR TIME2 =
    CALCULATE (
        MAX ( 'Table'[begin_date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[status] = SELECTEDVALUE ( 'Slicer 2'[status] )&&'Table'[call_num]=MAX('Table'[call_num])
        )
    )
RETURN
    IF (
        TIME2 > TIME1,
        DATEDIFF ( TIME1, TIME2, MINUTE ),
        DATEDIFF ( TIME2, TIME1, MINUTE )
    )

    Step 3， adjust relationship:

    And final will get want you want!

     

     

    Wish it is helpful for you!

     

    Best Regards

    Lucien

v-luwang-msft's avatar
v-luwang-msft
Community Support
5 years ago

Hi Anonymous ,

Try the following steps:

step1,create two slicer base on status,and create slicer:

then create measure on slicer 2:

Measure = IF(MAX('Slicer 2'[status])=SELECTEDVALUE(Slicer1[status]),BLANK(),1)

Step 2,create measure on base data table:

SILCERchoosedatediff = 
VAR TIME1 =
    CALCULATE (
        MAX ( 'Table'[begin_date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[status] = SELECTEDVALUE ( Slicer1[status] ) )
    )
VAR TIME2 =
    CALCULATE (
        MAX ( 'Table'[begin_date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[status] = SELECTEDVALUE ( 'Slicer 2'[status] )
        )
    )
RETURN
    IF (
        TIME2 > TIME1,
        DATEDIFF ( TIME1, TIME2, MINUTE ),
        DATEDIFF ( TIME2, TIME1, MINUTE )
    )

 

Final you will get you want !

It would probably be much easier to just use a slicer, the following is just a reference：

onlyoneslicer = CALCULATE(DATEDIFF(min('Table'[begin_date]),MAX('Table'[begin_date]),MINUTE),ALLSELECTED('Table'))

 

 

Wish it is helpful for you!

 

Best Regards

Lucien

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you very much v-luwang-msft, it is very helpful. But it only works if there is one call_num, is there a way to make the exact same thing for each call_num in the table? I am trying to do it unsuccessfully for the moment.

 

Thanks,

 

Regards,

 

Xavier