DAX to check if a value is duplicated in a column

[JohnDahis]

Some columns in the data table looks like below - I want to combine the values in two columns into one (if “Name” is not duplicated, joint it with “ID number”, otherwise show “-“)

I am using Excel as example to describe what I mean.

As there’s no COUNTIF in PowerBI, I don't know how to do it in DAX.

Can you please help me? Thank you.

[Pragati11 · Accepted solution]

Hi @JohnDahis ,

I have got following sample data:

Then I created following columns using DAX in Power BI:

countName = CALCULATE(COUNTROWS('Id Name'), FILTER(ALLSELECTED('Id Name'), 'Id Name'[Name] = EARLIER('Id Name'[Name])))

ConcatNameId = IF('Id Name'[countName] = 1, CONCATENATE('Id Name'[Name], 'Id Name'[ID]), "-")

When I move these columns to above sample data I get the required result as follows:

You see the desired result.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Pragati11 · Accepted solution]

Hi @JohnDahis ,

I have got following sample data:

Then I created following columns using DAX in Power BI:

countName = CALCULATE(COUNTROWS('Id Name'), FILTER(ALLSELECTED('Id Name'), 'Id Name'[Name] = EARLIER('Id Name'[Name])))

ConcatNameId = IF('Id Name'[countName] = 1, CONCATENATE('Id Name'[Name], 'Id Name'[ID]), "-")

When I move these columns to above sample data I get the required result as follows:

You see the desired result.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[JohnDahis]

@Pragati11, 

Thank you for your help and guidance

is there a way to keep one instance of the duplicates (either one)?

Thanks again.

[Pragati11 · Accepted solution]

Hi @JohnDahis ,

On what rule you want to keep the one instance? Is it ID or random or some other value in your data?

If it a single row against a NAME, you can get it in the table visual as follows:

On ID column, right click and Select LAST option.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

Thanks,

Pragati

[JohnDahis]

@Pragati11 

I meant, to have a DAX way to show either occurrence. The table is used as a data source.