Compare 2 Table columns based on filter applied

[streamly]

Hi Everyone!

I have 2 Tables that i want to compare the 2 columns based on the filter/slicer applied.

Table 1:

Date	Name	Email	Task
01/25/2020	A	Email A	Task 1
01/25/2020	A	Email A	Task 2
01/26/2020	C	Email C	Task 3
01/25/2020	B	Email B	Task 4
01/25/2020	B	Email B	Task 5
01/26/2020	C	Email C	Task 6

Table 2:

Name	Email
A	Email A
B	Email B
C	Email C

I have Date slicer and want to check if Name in Table 2 has entry Table 1 based on the Date slicer

Applied Date slicer/filter: 01/26/2020 in Table 1

Expected Result:

Name	Email	with Task (Y/N)
A	Email A	N
B	Email B	N
C	Email C	Y

If this is not possible, is there any way to get the same result. Thanks!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[streamly]

Hi Ashish,

Thanks for the reply. I've missed that Table 1 Dates are Week End Date it should look like this.

Table 1:
Date	Name	Email	Task
01/03/2020	A	Email A	Task 1
01/10/2020	A	Email A	Task 2
01/17/2020	C	Email C	Task 3
01/10/2020	B	Email B	Task 4
01/17/2020	B	Email B	Task 5
01/03/2020	C	Email C	Task 6

[Ashish_Mathur]

Hi,

My solution should still work.  If it does not, then please share that instance.

[v-frfei-msft · Accepted solution]

Hi @streamly ,

A sample for your reference.

Measure = 
VAR se =
    SELECTEDVALUE ( 'date'[Date] )
VAR yms =
    YEAR ( se ) * 100
        + WEEKNUM ( se )
VAR ymc =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            YEAR ( 'Table'[Date] ) * 100
                + WEEKNUM ( 'Table'[Date] ) = yms
        )
    )
RETURN
    IF ( ymc = BLANK (), "N", "Y" )

Pbix as attached.