Count Unique Rows using two columns

[Boycie92]

Hi,

I was wondering if someone can help

I have a model set up using direct query.

I have two column each have duplicates I want to be able to create a measure that would count only unique rows from BOTH columns

For Example:

Column A            Column B

1                              A

2                              B

3                              C

4                              D

1                              A

4                              E

3                              C

I was be expecting a total of 5 unique rows (what I want the measure to show).

As I have 2 rows that are Column A: 1 and Column B: A (should only count one row)

I also have two rows that are Column A: 3 and Column B: C (should only count one row)

I also have two other rows:

• 1st Row Column A: 4 and Column B: D
• 2nr Row Column A: 4 and Column B: E
• Both of these rows should be counted as they do not share the same values

Is there any way to achieve this?

Thanks in advance,

Boycie92

[v-huizhn-msft]

Hi @Boycie92,

After test, the measure formula @Zubair_Muhammad posted is right. You can create a card visual to show the result.



Please mark the right reply as answer, so more people will find workaround easily and clearly.

Best Regards,
Angelia

[RolandsP]

You could combine 2 columns into a new column:

Combine = COMBINEVALUES("_", Table[ColumnA], Table[ColumnB])

And then do a distinct count on the new column:

Unique rows = DISTINCTCOUNT(Table[Combine])

[Zubair_Muhammad · Accepted solution]

@Boycie92

MEASURE =
COUNTROWS ( DISTINCT ( Table_Name ) )