Grouping with Measure in Table Visual and Filtering from Unrelated Slicer

[Mann]

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/2020 3PM	3/20/2020 10PM
1	John	B	DS	21-Mar-20	3/21/2020 11AM	3/20/2020 11PM
2	Max	A	DS	18-Mar-20	3/18/2020 4AM	3/18/2020 9AM
2	Max	A	DS	18-Mar-20	3/18/2020 1PM	3/18/2020 8PM
2	Max	A	NS	18-Mar-20	3/18/2020 9PM	3/18/2020 11PM
2	Max	B	DS	20-Mar-20	3/20/2020 4AM	3/20/2020 2PM
2	Max	B	DS	20-Mar-20	3/20/2020 3PM	3/20/2020 10PM
3	Philip	C	NS	21-Mar-20	3/21/2020 1PM	3/21/2020 10PM
3	Philip	A	DS	22-Mar-20	3/22/2020 3PM	3/22/2020 8PM

I want to create a table visual from this considering slicers like below:

ID	Name	Supplier	Date	First Entry Time	Last Exit Time
1	John	A	20-Mar-20	3/20/2020 4AM	3/20/2020 10PM
1	John	B	21-Mar-20	3/21/2020 11AM	3/20/2020 11PM
2	Max	A	18-Mar-20	3/18/2020 4AM	3/18/2020 8PM
2	Max	B	20-Mar-20	3/20/2020 4AM	3/20/2020 2PM

There are two slicers on report:

(1) Date slicer coming from unrelated table 

(2) Shift Slicer coming from unrelated table

Note: Above example of table visual is when "DS" is selected from Shift Type slicer and Date= 18 March, 20 March, 21 March is selected from Date slicer. Table visual will not have any column of Shift.

I have few challenges to get the following done like:

1. I want to show first entry time and last entry time as per shift selection like if "DS" is selected then first entry time and last exit time needs to be calculated accordingly. Same way when NS is selected or both DS & NS is selected. Shift Slicer is coming from different table can't create relationships.

2. There is date slicer which is coming from a separate table and this table is not connected to the source table. (I can't create relationship due to some constraint). When any one or multiple dates are selected from this slicer final table visual should get filter for e.g. if date slicer selection is of "18 March" and "20 March" then records of 18 and 20 March should be shown.

Can we achieve the above without creating any intermediate calculated table or relationships. I can only work with measures.

Thanks

Mann

[V-lianl-msft]

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 · Accepted solution]

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.