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
deb_power123
Helper V
Helper V

Delete rows with duplicate entries in table using DAX or Tranformation

Hi,

I want to get rid of the rows containing Asia,Delhi then Asia,Shimla and Asia,Panjim as region and location in below table and also it has 100 enties like this[with same combination] .

 

Can we have any DAX or trasformation rule to handle this?

Input source table:

RegionlocationAveragemarksSchoolName
AsiaDelhi90ABC
IndiaDelhi90ABC
IndiaChennai85DEF
JapanTokyo88XYZ
AsiaShimla92WEF
IndiaShimla92WEF
AsiaPanjim78LMN
IndiaPanjim78LMN
AsiaGoa66JKL
AsiaHongkong71OPP
AsiaKarachi41LMI



Expected Output:

 

RegionlocationAveragemarksSchoolName
IndiaDelhi90ABC
IndiaChennai85DEF
JapanTokyo88XYZ
IndiaShimla92WEF
IndiaPanjim78LMN
AsiaGoa66JKL
AsiaHongkong71OPP
AsiaKarachi41LMI
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@deb_power123 , Create a flag like this

 

if([region] ="Asia" && [Location] in {"Delhi","Shimla","Panjim"} , 1, 0)

 

You can use that in measure or visual level filter

 

or create a new table

calculatetable(Table, [Flag]=0)

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@deb_power123 , Create a flag like this

 

if([region] ="Asia" && [Location] in {"Delhi","Shimla","Panjim"} , 1, 0)

 

You can use that in measure or visual level filter

 

or create a new table

calculatetable(Table, [Flag]=0)

sorry @amitchandak My bad it worked,I added wrong name for column.

Hi @amitchandak  I am not very clear with your approach, I tried the first approach but it shows only filter as 0 in the visual filter and 1 doesnt apprear.So, if 1 is not showing how will I be able to hide the entries for the first condition?Could you please suggest 

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.

Top Solution Authors