Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JohnDahis
Helper III
Helper III

DAX to check if a value is duplicated in a column

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.

 

Sample.png

2 ACCEPTED SOLUTIONS
Pragati11
Super User
Super User

Hi @JohnDahis ,

 

I have got following sample data:

nid2.png

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:
nid1.png
You see the desired result.
 
If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂
 
Thanks,
Pragati

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

View solution in original post

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:

nid3.png

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

View solution in original post

4 REPLIES 4
Pragati11
Super User
Super User

Hi @JohnDahis ,

 

I have got following sample data:

nid2.png

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:
nid1.png
You see the desired result.
 
If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂
 
Thanks,
Pragati

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

@Pragati11

Thank you for your help and guidance

 

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

 

Thanks again.

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:

nid3.png

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

@Pragati11 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.