cancel
Showing results 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

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:

 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?

1 ACCEPTED SOLUTION
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:

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!!

3 REPLIES 3
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:

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!!

Helper I

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

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors