Forum Discussion
Anonymous
5 years agoNot applicable
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...
- 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
5 years agoCommunity Support
Hi Anonymous ,
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
Anonymous
5 years agoNot applicable
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