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 ,

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's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi v-luwang-msft ,

    here an exemple : 

     

    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
    2-161130-000211/30/2016 0:1811/30/2016 0:19911
    2-161130-000211/30/2016 0:1911/30/2016 0:19trait
    2-161130-000211/30/2016 0:1911/30/2016 0:21cree
    2-161130-000211/30/2016 0:2111/30/2016 0:24repa
    2-161130-000211/30/2016 0:2411/30/2016 0:30rout
    2-161130-000211/30/2016 0:3011/30/2016 0:31lieu
    2-161130-000211/30/2016 0:3111/30/2016 1:09aupa
    2-161130-000211/30/2016 1:0911/30/2016 1:09tran
    2-161130-000211/30/2016 1:0911/30/2016 1:39dest
    2-161130-000211/30/2016 1:3911/30/2016 1:44libe
    2-161130-000211/30/2016 1:4411/30/2016 1:45reto
    2-161130-000211/30/2016 1:4511/30/2016 1:45arrzon
    2-161130-000211/30/2016 1:4511/30/2016 1:45comp
    2-161130-000211/30/2016 1:45 clas

     

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

     

    call_numbegin_date(911)begin_date(cree)date_diff(second)
    2-161130-000111/30/2016 0:1011/30/2016 0:1014.0000001
    2-161130-000211/30/2016 0:1811/30/2016 0:1924

     

    thank you,

     

    regards,

     

    Xavier