Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
P_P2595
Helper I
Helper I

Want to create a measure with no relation between two table

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:

IDRoleProvider
1PsyABC
2SpeechDEF
3OTXYZ
4STEDC

Table 2:

IDRoleProvider
1PsyABC
2SpeechDEF
6OTTGB
7SPQAZ

 

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

Table 1Table 2 Diff
422

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.

1 ACCEPTED SOLUTION
Kishore_KVN
Super User
Super User

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:

Kishore_KVN_0-1687861563540.png

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:

Kishore_KVN_1-1687861653258.png

 

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

View solution in original post

3 REPLIES 3
Kishore_KVN
Super User
Super User

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:

Kishore_KVN_0-1687861563540.png

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:

Kishore_KVN_1-1687861653258.png

 

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

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

cosm
Resolver II
Resolver II

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors