Forum Discussion

Regular Visitor

6 years ago

Solved

Count distinct values across two separate rows in two separate tables

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

You may try the measure below.

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

3 Replies

Anonymous
Not applicable
6 years ago
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
  Super User
  6 years ago
  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

Icon for Community Support rank

Community Support

You may try the measure below.

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