DAX to remove duplicate row entries

[deb_power123]

Hi,

For the source table below , I have repeated rows one for India and another for Delhi location .

Another for India and Goa location. 

I want to hide all those rows with India(marked in bold in the below table) location and want to only keep the rows with Delhi and Goa location .I have many enteries like this so it should be don via some DAX. Please suggest.

Source Indput:

School	location	Averagemarks
ABC	India	90
ABC	Delhi	90
DEF	India	89
DEF	Delhi	89
DEJ	India	75
DEJ	Goa	75
DEM	India	97
DEM	Goa	97

Expected Output :

School	location	Averagemarks
ABC	Delhi	90
DEF	Delhi	89
DEJ	Goa	75
DEM	Goa	97

Kind regards

Sameer

[v-easonf-msft]

Hi, @deb_power123 

If you want to use DAX to remove duplicate row entries, @koorosh 's solution could work for you.

If your problem has been solved, please accept @koorosh's reply as solution to close this thread, so that other community members will easily find the solution when they get the same issue.

Best Regards,
Community Support Team _ Eason

[deb_power123]

I want to get rid of Asia,Delhi then Asia,Shimla and Asia,Panjim in below table and also it has say 100 enties for the combinations .Can we have any DAX or trasformation rule to handle this?

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

[v-easonf-msft · Accepted solution]

Hi, @deb_power123 

No dax can identify whether the type of a field is a region or a country.

For the 100 entries for the combinations, you can only consider removing the data by looking for keywords.

Please add a filter measure and apply it to visual filter pane to filter data:

Ex:

Measure = IF(MAX('Table'[Region]) in {"Asia","Europe"},0,1)

or

Measure1 = IF(CONTAINSSTRING(MAX('Table'[Region]),"Asia")||CONTAINSSTRING(MAX('Table'[Region]),"Europe"),0,1)

Please let me know if I misunderstood.

Best Regards,
Community Support Team _ Eason

[koorosh]

CALCULATETABLE('Sheet1',KEEPFILTERS('Sheet1'[Location] in {"India"} = False))


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos!

[johnt75]

Change the visual filter on the table to exclude India