Are values from Table2 present in Table1 (with filters present on table 2)

[Moonc]

I have two tables that are identical in my data model. The only difference is that Table2 is being filtered by the URL given for the report.

I need to be able to tell what values are present in Table2 in Table1.

Table 1 (Expected Result, actual result is passing all TRUE because [Present] is finding all values in both tables)

UserGroup1	UserName1	Present
A	C	True
A	D	False

Present = CALCULATE(COUNTROWS(TABLE2),FILTER(TABLE2,Table2[UserName2] = Table1[UserName1])) > 0

Table2 (this table actually contains both UserName C and D, but its just filtered via the URL passed for this user)

UserGroup2	UserName2
A	C

I have tried putting ALLSELECTED() around table2 in the filter, but I think I am misunderstanding how that function works.

[Moonc]

Side note, my formula works if I change the data in table2 to only have the values that I need. But in the real scenario, table2 will be filtered by the URL of the report.

[FreemanZ]

hi @Moonc 

Are table1 and table2 dataset tables or visuals tables?

make some screenshot if you are not sure. 

[Moonc · Accepted solution]

@FreemanZ - They are dataset tables.

I was able to solve my problem by shifting my approach and using a measure.

=COUNTROWS(FILTER(TABLE1,NOT(CONTAINS(TABLE2,TABLE2[UserName2],TABLE1[UserName1]))))

When the user is filtering table1 (through a slicer), the count will be (blank) until the only value(s) selected are currently present in Table2.

I am then using this count to dynamically change measures across the pages. Very niche use case, but I got it to work.

[Greg_Deckler]

@Moonc Not sure I fully understand the actual data in the data model but you can use INTERSECT to determine which values are present in both tables.

[Moonc]

Intersect has the same problem as my present calculate column has (it will compare ALL values of table1 to table 2). I need table2 to be passing ONLY values for what is currently filtered.The table itself has all of the same values that Table1 has, but it will be filtered by the URL of the report.

Edit: to clarify a bit

Table1 (Expected result, UserName1 = D is false because it is not selected in the filter)

UserGroup1	UserName1	Present
A	C	True
A	D	False

Table2 (Unfiltered)

UserGroup2	UserName2
A	C
A	D

Table2 (filtered by slicer (hidden to user))

UserGroup2	UserName2
A	C

Table 2 is always going to filtered. Whenever I try to do comparisons between the two tables, it is comparing the unfiltered version. I need it to compare the filtered version