How to compare two tables for same column value

[POSPOS]

Hi All,

I have a requirement to compare two tables and check if the column values in one table exists in another.

Two tables:

1. Master Table -- This table has row level security on user name

2. Detail Table 

3. I have created a Custom table to only get the columns values from Master table based on the user logged in and pass that to the Detail table.

Sample pbix - Download from here

Master Table : 

User Name	Center	Location
G1000	1760000000	53032519
G1000	1760000001	53054608
G1000	1760100000	53054614
G1000	1760101000	53054618
G1000	1760101100	53054619
R0000	1760101300	53054617
R0000	1760103000	53054612
R0000	1760105000	53054609
R0000	1760106000	53054620
R0000	1760107000	53054615
R0000	1760108000	53054616
R0000	1760200000	53054613

Detail Table : 

Center	Location	Sales	Expense
1030000001	53054612	57624	57624
1030000005	53054617	39910.32	39910.32
1030000009	53054612	98954	98954
1070000002	53054617	74842.68	74842.68
1300000000	53054620	83662	83662
1300702000	53054612	155208	155208
1760000000	53054612	139836	139836
1760000000	53054617	155916.6	155916.6
1760000000	53054618	127012	127012
1760000000	53054620	53014	53014
1760000001	53032519	1825240	1825240
1760000001	53054609	488706	488706
1760000001	53054612	1257996	1257996
1760000001	53054615	172881	172881
1760000001	53054616	193708	193708
1760000001	53054617	398268	398268
1760000001	53054618	326342	326342
1760000001	53054619	1859879	1859879
1760000001	53054620	90550	90550
1760106000	53054615	196028	196028
1900000000	53054608	638880	638880
1900080000	53032519	532061.6	532061.6
1900080000	53054608	141972	141972
1900080000	53054609	7928726	7928726
1900080000	53054612	4214294	4214294
1900080000	53054615	754020	754020
1900080000	53054616	446284	446284
1900080000	53054617	113322	113322
1900080000	53054618	2715564	2715564
1900080000	53054619	3013646	3013646
1900080000	53054620	264524	264524
1950000000	53054609	215120	215120
1950000000	53054615	352240	352240
1950000000	53054616	712994	712994
1950000000	53054617	345190.5	345190.5
1950000000	53054618	455206	455206
1950000000	53054619	598120	598120

Custom Table I created:

Custom = 
SUMMARIZE('Master Table','Master Table'[User Name],'Master Table'[Location],'Master Table'[Center])

Now, my requirement is, after a user logs in, the Master table gets filtered to the logged in user, I now want to compare Custom Table and Detail Table i.e., if the col "Location" from custom table exists in the Detail Table. I want to create a calculated column in the "Detail Table" as Exist/NA which is further used for other calculations.

Eg: when user G1000 logs in, then I want to compare Location from both Detail table and Custom table -> Create a col in Detail table saying if it exists or no.

Can somone please suggest on how to achieve this?

Thank you.

[Anonymous]

Hi @POSPOS,

We have provided the required DAX expression and attached a screenshot for your reference. Kindly review it at your earliest convenience and let us know if it meets your needs.

we would appreciate it if you could Accept it as a solution and drop a 'Kudos' so other members can find it more easily.

Best regards,

Shamili.v

[Anonymous]

Hi @POSPOS 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[Anonymous]

Hi @POSPOS 
We haven’t heard back since our last response and wanted to check if your query has been resolved. If not, please feel free to reach out for further assistance. If it has been resolved, kindly mark the helpful reply as the solution to make it easier for others to find. A kudos would also be greatly appreciated!
Thank you.

[Anonymous]

Hi @POSPOS 
Thank you for your patience. From the steps outlined , please let us know if anything was helpful to you, so that we can convert it into a formal answer. If so, we would appreciate it if you could Accept it as a solution and drop a 'Kudos' so other members can find it more easily.
Thank you.

[Anonymous · Accepted solution]

 Hi @POSPOS 

Thank you for providing Sample .Pbix file.
use below DAX Expression and create a calculated column:

Exist Status =

IF(

   ISBLANK(RELATED('Custom'[Location])),

   "NA",

   "Exists"

)

Certain records in the detail table, specifically in the center column, do not have corresponding entries in the center column of the custom table, where a one-to-many relationship has been established. In cases where no matching records are found, the result should return as "NA". .

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

Appreciate your Kudos!! 

Thank you

[Anonymous]

Hi @POSPOS

Thank you for reaching out Microsoft fabric community forum.

The sample PBIX file provided is inaccessible
Please provide sample data that covers your issue or question completely, in a usable format.

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you

[POSPOS]

@Anonymous  - File is available to download here

[POSPOS]

@Anonymous  - As the file was inaccessible, I have reattached the file to download.

[VahidDM]

Hi @POSPOS 

You can create a calculated column in the Detail Table using CONTAINS to check if the Detail Table’s Location exists in the filtered Custom Table:

Exist Status =
IF(
    CONTAINS(
        Custom, 
        Custom[Location], 
        Detail[Location]
    ),
    "Exist",
    "NA"
)

This column returns "Exist" if the Detail Table’s Location is found in the Custom Table (which is filtered by the logged-in user’s context), otherwise "NA".

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

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[POSPOS]

@VahidDM  - Thanks for your response, Solution is not working as expected.
Result is showing as Exist even if the value is not exisiting in the custom table. 

DAX provided is not taking the filter context based on the user. It is looking at the entire data set instead.