Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I would like to compare the columns between two tables example all the values from table1 to all all the values in table2 based on unique value. Considering 20 Columns in the table.
How do we create a DAX formula for this?
Primary Key | Existing in Table1 | Existing in table2 | Columns Not Match | Match% |
11 | Yes | Yes | 10 | 50% |
12 | Yes | No | 20 | 0% |
13 | No | Yes | 20 | 0% |
Solved! Go to Solution.
Hi @Anonymous
Due to I don't know your data model, I build two sample tables to have a test.
Table1:
Table2:
Build a Union table by Dax.
UnionTable = SUMMARIZE(UNION(Table1,Table2),[Primary Key],[Value])
Table1Match =
VAR _Value = CALCULATE(SUM(Table1[Value]),FILTER(Table1,Table1[Primary Key]=EARLIER(UnionTable[Primary Key])&&Table1[Value]=EARLIER(UnionTable[Value])))
Return
IF(ISBLANK(_Value),0,1)
Table2Match =
VAR _Value = CALCULATE(SUM(Table2[Value]),FILTER(Table2,Table2[Primary Key]=EARLIER(UnionTable[Primary Key])&&Table2[Value]=EARLIER(UnionTable[Value])))
Return
IF(ISBLANK(_Value),0,1)
Then let's build a compare table.
Compare Table =
ADDCOLUMNS (
GENERATESERIES ( 11, 13, 1 ),
"Existing in Table1", IF ( [Value] IN VALUES ( Table1[Primary Key] ), "Yes", "No" ),
"Existing in Table2", IF ( [Value] IN VALUES ( Table2[Primary Key] ), "Yes", "No" )
)
Columns Not Match =
VAR _Match =
COUNTAX (
FILTER (
UnionTable,
UnionTable[Table1Match] = 1
&& UnionTable[Table2Match] = 1
&& UnionTable[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
),
UnionTable[Primary Key]
)
VAR _all =
IF (
'Compare Table'[Existing in Table1] = "Yes",
COUNTAX (
FILTER (
Table1,
Table1[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
),
Table1[Primary Key]
),
COUNTAX (
FILTER (
Table2,
Table2[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
),
Table2[Primary Key]
)
)
RETURN
_all - _Match
Match% =
VAR _Match =
COUNTAX (
FILTER (
UnionTable,
UnionTable[Table1Match] = 1
&& UnionTable[Table2Match] = 1
&& UnionTable[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
),
UnionTable[Primary Key]
)
VAR _all =
IF (
'Compare Table'[Existing in Table1] = "Yes",
COUNTAX (
FILTER (
Table1,
Table1[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
),
Table1[Primary Key]
),
COUNTAX (
FILTER (
Table2,
Table2[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
),
Table2[Primary Key]
)
)
VAR _Result = _Match/_all
RETURN
IF(_Result = BLANK(),0,_Result)
Result is as below.
You can download the pbix file from this link: Sample
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
Hi @Anonymous
Due to I don't know your data model, I build two sample tables to have a test.
Table1:
Table2:
Build a Union table by Dax.
UnionTable = SUMMARIZE(UNION(Table1,Table2),[Primary Key],[Value])
Table1Match =
VAR _Value = CALCULATE(SUM(Table1[Value]),FILTER(Table1,Table1[Primary Key]=EARLIER(UnionTable[Primary Key])&&Table1[Value]=EARLIER(UnionTable[Value])))
Return
IF(ISBLANK(_Value),0,1)
Table2Match =
VAR _Value = CALCULATE(SUM(Table2[Value]),FILTER(Table2,Table2[Primary Key]=EARLIER(UnionTable[Primary Key])&&Table2[Value]=EARLIER(UnionTable[Value])))
Return
IF(ISBLANK(_Value),0,1)
Then let's build a compare table.
Compare Table =
ADDCOLUMNS (
GENERATESERIES ( 11, 13, 1 ),
"Existing in Table1", IF ( [Value] IN VALUES ( Table1[Primary Key] ), "Yes", "No" ),
"Existing in Table2", IF ( [Value] IN VALUES ( Table2[Primary Key] ), "Yes", "No" )
)
Columns Not Match =
VAR _Match =
COUNTAX (
FILTER (
UnionTable,
UnionTable[Table1Match] = 1
&& UnionTable[Table2Match] = 1
&& UnionTable[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
),
UnionTable[Primary Key]
)
VAR _all =
IF (
'Compare Table'[Existing in Table1] = "Yes",
COUNTAX (
FILTER (
Table1,
Table1[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
),
Table1[Primary Key]
),
COUNTAX (
FILTER (
Table2,
Table2[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
),
Table2[Primary Key]
)
)
RETURN
_all - _Match
Match% =
VAR _Match =
COUNTAX (
FILTER (
UnionTable,
UnionTable[Table1Match] = 1
&& UnionTable[Table2Match] = 1
&& UnionTable[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
),
UnionTable[Primary Key]
)
VAR _all =
IF (
'Compare Table'[Existing in Table1] = "Yes",
COUNTAX (
FILTER (
Table1,
Table1[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
),
Table1[Primary Key]
),
COUNTAX (
FILTER (
Table2,
Table2[Primary Key] = EARLIER ( 'Compare Table'[Primary Key] )
),
Table2[Primary Key]
)
)
VAR _Result = _Match/_all
RETURN
IF(_Result = BLANK(),0,_Result)
Result is as below.
You can download the pbix file from this link: Sample
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Could you show me a sample like the two tables you are dealing with?
Or you can share your pbix file with me by your Onedrive for Business.
This will make it easier for me to understand your requirement.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |