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.

     

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