Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to remove rows based on conditions

Hi All,   I have a data set like below. My requirement is to remove the duplicate entries based on other columns values. Each strategy name is repeated multiple times with the same USD value. And ...
Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    as you have a requirement "If it is approved and not apporved both then only one row with approved status" try a calculated table

    Table = SUMMARIZE('Table1';
Table1[Strategy name];Table1[Value];"Approval Status";MIN(Table1[Approval Status]))

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    Sorry Anonymous 

     

    Some modification required in DAX.

     

    Condition Check = (Test[Approval Status]="Not Approved" && NOT(ISEMPTY(FILTER(Test,Test[Strategy name]=EARLIER(Test[Strategy name]) && Test[Value]=EARLIER(Test[Value]) && Test[Approval Status]="Approved"))))
     
    and filter it to FALSE only.
     
     
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    Hi Anonymous ,

    You can try the following methods:
    1. As some previous responses, first, remove Duplicates in the Query Editor.


    2. Then go back to the Data View and create the calculated column as follows:

    Column =
Test[Approval Status] = "Not Approved"
    && CALCULATE (
        MIN ( Test[Approval Status] ),
        ALLEXCEPT ( Test, Test[Strategy name] )
    ) = "Approved"

    3. Filter values in the calculated column that are equal to False:

    Here is a demo, please try it:

    https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ea9CbtwjxyNFp2tCMhjtBFEB0YTYjOYE4G-3sZhGv4bNmA?e=2PNDNT

    Best Regards,
    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

az38's avatar
az38
Community Champion
6 years ago

Hi Anonymous 

as you have a requirement "If it is approved and not apporved both then only one row with approved status" try a calculated table

Table = SUMMARIZE('Table1';
Table1[Strategy name];Table1[Value];"Approval Status";MIN(Table1[Approval Status]))

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi az38, 

 

Thanks for the help. I have created calculated tables using your solution and using max(date_created) as using only approval_status was still giving me duplicates and it seems to work now. I am a bit new in Dax so could you please expalin to me how MIN(Approval_status) will pick up 'Approved' whenever it gets 'Approved & Not Approved for the same strategy ? 

 

Though it is working fine I need to apply the same for other datasets also so just wanted to be a bit more clear on this.

 

Once again many thanks for your help.

 

Regards

Ankhi

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    Approval = MIN because it starts from "A" 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      oh okk 🙂

       

      Thank you