Forum Discussion

Mann's avatar
Mann
Resolver III
6 years ago
Solved

Grouping with Measure in Table Visual and Filtering from Unrelated Slicer

Hi Guys,   I have table in source like this: ID Name Supplier Shift Date Entry Time Exit Time 1 John A DS 20-Mar-20 3/20/2020 4AM 3/20/2020 2PM 1 John A DS 20-Mar-20 3/20...
Need Help
  • Mann's avatar
    Mann
    6 years ago

    Hi Liang,

     

    Thanks for checking on this. I checked the report attached. 

    First thing: Date slicer and Shift slicer are coming from two different unrelated tables.

     

    Also, I managed to find the solution without showing shift type in table:

    I created "Test Date" measure which I added in visual level filter and filter it to always 1 as shown:

     

    Test Date = 
If(SELECTEDVALUE('table'[Date]) IN VALUES('Table 2'[date]),1,0)

     

     Then I created the below two measures for First Entry Time and Last Exit Time:

     

     

    First Entry Time = 
VAR DS=
CALCULATE (
    FIRSTNONBLANK ( 'table'[Entry Time], 1 ),
    'table'[Shift] = "DS"
)
VAR NS=
CALCULATE (
    FIRSTNONBLANK ( 'table'[Entry Time], 1 ),
    'table'[Shift] = "NS"
)
VAR DSNS=
CALCULATE(
    FIRSTNONBLANK ( 'table'[Entry Time], 1 ))
Return
SWITCH(TRUE(),
SELECTEDVALUE('Table 3'[shift])= "DS",DS,
SELECTEDVALUE('Table 3'[shift])= "NS",NS,DSNS)

     

     

    Last Exit Time = 
VAR DS=
CALCULATE (
    LASTNONBLANK ( 'table'[Exit Time], 1 ),
    'table'[Shift] = "DS"
)
VAR NS=
CALCULATE (
    LASTNONBLANK ( 'table'[Exit Time], 1 ),
    'table'[Shift] = "NS"
)
VAR DSNS=
CALCULATE(
    LASTNONBLANK ( 'table'[Exit Time], 1 ))
Return
SWITCH(TRUE(),
SELECTEDVALUE('Table 3'[shift])= "DS",DS,
SELECTEDVALUE('Table 3'[shift])= "NS",NS,DSNS)

    Thanks for your help on this.

     

    Mann.

V-lianl-msft's avatar
V-lianl-msft
Community Support
6 years ago

Hi Mann ,

 

I created three measures.

 

First Entry Time = CALCULATE(MIN('table'[Entry Time]),ALLEXCEPT('table','table'[Date],'table'[ID],'table'[Supplier],'table'[Shift]))

Last Exit Time = CALCULATE(MAX('table'[Exit Time]),ALLEXCEPT('table','table'[ID],'table'[Date],'table'[Supplier],'table'[Shift]))

condiition = IF(MAX('table'[Date])in VALUES('Table 2'[date])&&(MAX('table'[Shift])in VALUES('Table 2'[shift])),1)

 

 

When I add [shift] column to the chart and apply [condition] to the visual level filter, I can get the result you want.

Sample .pbix

 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Mann's avatar
Mann
Resolver III
6 years ago

Hi Liang,

 

Thanks for checking on this. I checked the report attached. 

First thing: Date slicer and Shift slicer are coming from two different unrelated tables.

 

Also, I managed to find the solution without showing shift type in table:

I created "Test Date" measure which I added in visual level filter and filter it to always 1 as shown:

 

Test Date = 
If(SELECTEDVALUE('table'[Date]) IN VALUES('Table 2'[date]),1,0)

 

 Then I created the below two measures for First Entry Time and Last Exit Time:

 

 

First Entry Time = 
VAR DS=
CALCULATE (
    FIRSTNONBLANK ( 'table'[Entry Time], 1 ),
    'table'[Shift] = "DS"
)
VAR NS=
CALCULATE (
    FIRSTNONBLANK ( 'table'[Entry Time], 1 ),
    'table'[Shift] = "NS"
)
VAR DSNS=
CALCULATE(
    FIRSTNONBLANK ( 'table'[Entry Time], 1 ))
Return
SWITCH(TRUE(),
SELECTEDVALUE('Table 3'[shift])= "DS",DS,
SELECTEDVALUE('Table 3'[shift])= "NS",NS,DSNS)

 

 

Last Exit Time = 
VAR DS=
CALCULATE (
    LASTNONBLANK ( 'table'[Exit Time], 1 ),
    'table'[Shift] = "DS"
)
VAR NS=
CALCULATE (
    LASTNONBLANK ( 'table'[Exit Time], 1 ),
    'table'[Shift] = "NS"
)
VAR DSNS=
CALCULATE(
    LASTNONBLANK ( 'table'[Exit Time], 1 ))
Return
SWITCH(TRUE(),
SELECTEDVALUE('Table 3'[shift])= "DS",DS,
SELECTEDVALUE('Table 3'[shift])= "NS",NS,DSNS)

Thanks for your help on this.

 

Mann.