Count distinct values across two separate rows in two separate tables

[nvizard]

Hi all,

I am trying to count the number of distinct values that occur in two separate columns in two separate tables (count the number of distinct values as though the two columns were appended) 

For example, if I had the two columns below :

1                       1

2                       3

3                       5

4                       7

5                       9

it should return a count of 7, 3 values were not counted (1,3,5) as they appeared twice.

Many thanks for your help

[v-chuncz-msft · Accepted solution]

@nvizard 

You may try the measure below.

Measure =
COUNTROWS (
    DISTINCT ( UNION ( VALUES ( Table1[Column1] ), VALUES ( Table2[Column1] ) ) )
)

[Anonymous]

just do union of these two columns which will remove duplicates and create new table with unique values.

New table=Union(table1,table2)

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[amitchandak]

Try one of the two

EXCEPT(union(all(table[column1]),all(table[column1])),INTERSECT(all(table[column1]), all(table[column2])))
or
EXCEPT(union(distinct(table[column1]),distinct(table[column1])),INTERSECT(distinct(table[column1]), distinct(table[column2])))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin