Forum Discussion

deb_power123's avatar
deb_power123
Icon for Helper V rankHelper V
4 years ago
Solved

DAX to remove duplicate row entries

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 i...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
deb_power123
Icon for Helper V rankHelper V
4 years ago

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?

 

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



  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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