2 slicers from same columns

[XavierC2]

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	2021-06-25 13:05:24
1	in progress	2021-06-25 13:05:24	2021-06-25 14:25:01
1	close	2021-06-25 14:25:01	2021-06-25 14:25:02
1	archieved	2021-06-25 14:25:02	null

I need to dynamically calculate intervals between choosen status selected by 2 slicers.

So, I need to make 2 slicers on the same field(the second slicer should not show the value selected in the first slicer) and calculate de datediff between the begin date from the first slicer and the begin date from the second slicer.

thank you for your help,

Regards

[v-luwang-msft · Accepted solution]

Hi @XavierC2 ,

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

[XavierC2]

Many thanks for you help!! you rock!

[v-luwang-msft]

Hi @XavierC2 ,

Will different  call_num  with the same status? When this is the case, and a status corresponds to more than one beginning_date, calculate which datediff to choose.Need more details.

Best Regards

Lucien

[XavierC2]

Hi @v-luwang-msft ,

here an exemple : 

2-161130-0001	11/30/2016 0:10	11/30/2016 0:10	911
2-161130-0001	11/30/2016 0:10	11/30/2016 0:10	trait
2-161130-0001	11/30/2016 0:10	11/30/2016 0:12	cree
2-161130-0001	11/30/2016 0:12	11/30/2016 0:19	repa
2-161130-0001	11/30/2016 0:19	11/30/2016 0:24	rout
2-161130-0001	11/30/2016 0:24	11/30/2016 0:44	lieu
2-161130-0001	11/30/2016 0:44	11/30/2016 1:11	tran
2-161130-0001	11/30/2016 1:11	11/30/2016 1:24	dest
2-161130-0001	11/30/2016 1:24	11/30/2016 1:40	libe
2-161130-0001	11/30/2016 1:40	11/30/2016 2:04	reto
2-161130-0001	11/30/2016 2:04	11/30/2016 3:10	comp
2-161130-0001	11/30/2016 3:10		clas
2-161130-0002	11/30/2016 0:18	11/30/2016 0:19	911
2-161130-0002	11/30/2016 0:19	11/30/2016 0:19	trait
2-161130-0002	11/30/2016 0:19	11/30/2016 0:21	cree
2-161130-0002	11/30/2016 0:21	11/30/2016 0:24	repa
2-161130-0002	11/30/2016 0:24	11/30/2016 0:30	rout
2-161130-0002	11/30/2016 0:30	11/30/2016 0:31	lieu
2-161130-0002	11/30/2016 0:31	11/30/2016 1:09	aupa
2-161130-0002	11/30/2016 1:09	11/30/2016 1:09	tran
2-161130-0002	11/30/2016 1:09	11/30/2016 1:39	dest
2-161130-0002	11/30/2016 1:39	11/30/2016 1:44	libe
2-161130-0002	11/30/2016 1:44	11/30/2016 1:45	reto
2-161130-0002	11/30/2016 1:45	11/30/2016 1:45	arrzon
2-161130-0002	11/30/2016 1:45	11/30/2016 1:45	comp
2-161130-0002	11/30/2016 1:45		clas

Let's say I am filtering on 911 and cree status, the result should be like this :

call_num	begin_date(911)	begin_date(cree)	date_diff(second)
2-161130-0001	11/30/2016 0:10	11/30/2016 0:10	14.0000001
2-161130-0002	11/30/2016 0:18	11/30/2016 0:19	24

thank you,

regards,

Xavier

[v-luwang-msft]

Hi @XavierC2 ,

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

[XavierC2]

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

[v-luwang-msft]

Hi @XavierC2 ,

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

[XavierC2]

Hi @v-luwang-msft,

here some real data

call_num	begin_date	end_date	status
2-161130-0001	11/30/2016 0:10	11/30/2016 0:10	911
2-161130-0001	11/30/2016 0:10	11/30/2016 0:10	trait
2-161130-0001	11/30/2016 0:10	11/30/2016 0:12	cree
2-161130-0001	11/30/2016 0:12	11/30/2016 0:19	repa
2-161130-0001	11/30/2016 0:19	11/30/2016 0:24	rout
2-161130-0001	11/30/2016 0:24	11/30/2016 0:44	lieu
2-161130-0001	11/30/2016 0:44	11/30/2016 1:11	tran
2-161130-0001	11/30/2016 1:11	11/30/2016 1:24	dest
2-161130-0001	11/30/2016 1:24	11/30/2016 1:40	libe
2-161130-0001	11/30/2016 1:40	11/30/2016 2:04	reto
2-161130-0001	11/30/2016 2:04	11/30/2016 3:10	comp
2-161130-0001	11/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