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 ,

Not very clearly.

Could you pls provide a sample?

Just for the table ,Slicer A choose value(begin_date):

2021-06-25 13:05:22

 And Slicer B choose value(begin_date):

2021-06-25 14:25:02

And you want to get the time between the two time?

 

 

Best Regards

Lucien

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi v-luwang-msft,

 

here some real data

call_numbegin_dateend_datestatus
2-161130-000111/30/2016 0:1011/30/2016 0:10911
2-161130-000111/30/2016 0:1011/30/2016 0:10trait
2-161130-000111/30/2016 0:1011/30/2016 0:12cree
2-161130-000111/30/2016 0:1211/30/2016 0:19repa
2-161130-000111/30/2016 0:1911/30/2016 0:24rout
2-161130-000111/30/2016 0:2411/30/2016 0:44lieu
2-161130-000111/30/2016 0:4411/30/2016 1:11tran
2-161130-000111/30/2016 1:1111/30/2016 1:24dest
2-161130-000111/30/2016 1:2411/30/2016 1:40libe
2-161130-000111/30/2016 1:4011/30/2016 2:04reto
2-161130-000111/30/2016 2:0411/30/2016 3:10comp
2-161130-000111/30/2016 3:10 clas

 

I need people to be able to have intervals between any status :

 

For example, I need people select in a first dropdown the status "tran" and in a second dropdown choose the status "comp"(in the 2nd dropdown, the status "tran" should not be displayed. Once the 2 status are selected, interval between the 2 begin_date should be automatically calculated for every single call_num.

 

Is that clear enough?

 

thank you,

 

regards,

 

Xavier