Get sum of same items in 2 columns

[YerlaSDU]

Hi folks,

Please help!

I have a 2 columns from 2 different tables. 

1st column have  55 unique items, 2nd column 106

How can I get the sum of unique items in both columns in comparison 

[Ashish_Mathur]

Hi,

Does this measure work?

=countrows(generate(values(Table1[column1]),values(Table2[column2])))

Hope this helps.

[YerlaSDU]

@Ashish_Mathur 

not working also 😞

[Ashish_Mathur]

Share the download link of your PBI file and show the expected result clearly.

[YerlaSDU]

@Ashish_Mathur 

There are link to the pbix

 I want to create new column "Mapped SubClass" with "SubClass from MDS", if there is no, then with "SubClass from MDS by name"

[Ashish_Mathur]

The answer should be 233.  This measure works

Measure = COUNTROWS(filter(SUMMARIZE('DWH DimEquipmentSERIP','DWH DimEquipmentSERIP'[EquipmentSubClass],'DWH DimEquipmentSERIP'[EquipmentSubClassName]),[EquipmentSubClass]<>BLANK()&&'DWH DimEquipmentSERIP'[EquipmentSubClassName]<>BLANK()))

Hope this helps.

[v-yueyunzh-msft]

Hi , @YerlaSDU 

For my first resolution, you just need to update the "return":

Measure = var _table1 = VALUES( 'Table1'[Column1] )
var _table2 =VALUES(Table2[Column1])
var _intersect_count =COUNTROWS( INTERSECT(_table1 , _table2))
return
_intersect_count

Then you can get the unique count,like this:

Best Regards,

Aniya Zhang

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

[YerlaSDU]

Yes, it shows matched items with SubClassCodes. How can I also compare unmatched SubClassCode items  with SubClassName column? 

[v-yueyunzh-msft · Accepted solution]

Hi , @Thennarasu_R 

You can use this dax :

Measure = var _table1 = VALUES( 'Table1'[Column1] )
var _table2 =VALUES(Table2[Column1])
var _intersect_count =COUNTROWS( INTERSECT(_table1 , _table2))
return
COUNTROWS( _table1) +COUNTROWS(_table2 )- 2* _intersect_count

This is my test result:

Best Regards,

Aniya Zhang

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

[YerlaSDU]

@v-yueyunzh-msftIt doesn't show me unique items, that 36

[v-yueyunzh-msft]

Hi , @YerlaSDU 

I see you have marked this problem, please ask if your problem is solved, if not, you can provide your sample data (.pbix or table)?

Best Regards,

Aniya Zhang

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

[YerlaSDU]

@v-yueyunzh-msft 

Here are example 

[Thennarasu_R]

What Dax Function You has use?

Try out  Distictcount Function..

[iceparrot]

Hey, 
maybe the solution posted in this thread will help to solve your problem: 
https://community.powerbi.com/t5/Desktop/distinct-count-of-multiple-columns/m-p/433060 

[YerlaSDU]

@iceparrot it not working for columns in different tables 

[Thennarasu_R]

Use DistinctCount Function With Sperate column

[YerlaSDU]

@Thennarasu_R 

Same result) Not working also(