Want to create a measure with no relation between two table

[P_P2595]

Hi All,

I would like to create a measure to check if the IDs are present in both tables or not but there is no relationship between the

two tables.
Table 1:

ID	Role	Provider
1	Psy	ABC
2	Speech	DEF
3	OT	XYZ
4	ST	EDC

Table 2:

ID	Role	Provider
1	Psy	ABC
2	Speech	DEF
6	OT	TGB
7	SP	QAZ

I want to create a measure to find the difference of values between two tables.
Output:

Table 1	Table 2	Diff
4	2	2

So  4 is total IDs from the first table, 2 is matching IDs from the 2nd table and the difference is 2 which is the last two IDs are not in second table.
Is this doable in power bi?

Thanks in advance.

[Kishore_KVN · Accepted solution]

Hello @P_P2595 
Create one column in Table 1 to get the matching records in Table 2 and its calculation looks as below:

Table 2 ID = LOOKUPVALUE('Table 2'[ID],'Table 2'[ID],'Table 1'[ID])

Output looks as below:

Now create three measures as below:

Total ID's in Table 1 = COUNT('Table 1'[ID])

Matching with Table 2 = 
Var A = COUNT('Table 2'[ID])
Var B = COUNT('Table 1'[Table 2 ID])
Return
A-B

Difference = [Total ID's in Table 1] - [Matching with Table 2]

Drag all three into a table visual your output looks as below:

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[P_P2595]

@Kishore_KVN Thank you so much, It's work for me.😊

[Anonymous]

Hi @P_P2595 .

There are maybe several ways to create the desired output.

One way is to create the output table by
- selecting New table, from "Model view"
- Implementing DAX-logic to derive the following columns:
--- count of distinct id's in table 1   (e.g. 4)
--- count of distinct id's in both table 1 and 2 (e.g. 2)
--- Ids in table 1 but not in table 2 (e.g. 4-2=2)

Kind regards.