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

Try this one,

 

1.First go to QUery editior

2.Select these three columns only by pressing ctrl + Click on columns one by one(Strategy Name,Value,Approval status)

3.Remove deplicate rows.

4.Close & save.

 

Now create one calculate column with below dax.

 

Condition Check = Test[Approval Status]="Approved" || (Test[Approval Status]="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 TRUE only.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.
     
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      If it resolves your problem mark it as solution and give kudos.

       

      Thanks & regards,

      Pravin Wattamwar

      www.linkedin.com/in/pravin-p-wattamwar

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        One more way is just simple create index column in your table.

        add below two columns.

         

        Duplicate_Check = VAr new=LOOKUPVALUE(Test[Strategy name],Test[Index],Test[Index]-1,Test[Approval Status],Test[Approval Status],Test[Value],Test[Value])
        return IF(Test[Strategy name]=new,-1,1)
         
        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  Duplicate_Check to 1 and Condition Check=Flase at visual level filter.
         
        If it resolve your problem mark it as a solution and give kudos.
         
        Thanks & regards,
        Pravin Wattamwar
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi PR,

       

      Thanks a lot for the solution. I have tried only the below one as of now and it works fine. Only one question , Can i create this in the query editior as e new column and filter it as false so my dataset has only these rows ? Or else I think for evry visual that I create I have to use this as filter and set it to false. Please let me know.

       

      Many Thanks

      Regards

      Ankhi