Delete rows with duplicate entries in table using DAX or Tranformation

[deb_power123]

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:

Region	location	Averagemarks	SchoolName
Asia	Delhi	90	ABC
India	Delhi	90	ABC
India	Chennai	85	DEF
Japan	Tokyo	88	XYZ
Asia	Shimla	92	WEF
India	Shimla	92	WEF
Asia	Panjim	78	LMN
India	Panjim	78	LMN
Asia	Goa	66	JKL
Asia	Hongkong	71	OPP
Asia	Karachi	41	LMI

Expected Output:

Region	location	Averagemarks	SchoolName
India	Delhi	90	ABC
India	Chennai	85	DEF
Japan	Tokyo	88	XYZ
India	Shimla	92	WEF
India	Panjim	78	LMN
Asia	Goa	66	JKL
Asia	Hongkong	71	OPP
Asia	Karachi	41	LMI

[amitchandak · Accepted solution]

@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)

[deb_power123]

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

[deb_power123]

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