Remove rows conditional

[Anonymous]

Hi,

How can I create a NEW table with :

Name	Status	Date
A	S1	1/1/2018
A	S2	1/15/2018
A	S3	3/7/2018
B	S1	2/3/2018
B	S2	4/2/2018
C	S1	6/1/2018

How can I remove all the rows containing the status of "S3"? The O/P should be (in a new table as I want to retain both) :-

Name	Status	Date
B	S1	2/3/2018
B	S2	4/2/2018
C	S1	6/1/2018

[parry2k · Accepted solution]

@Anonymous following dax for new table

New Table = 
CALULATETABLE ( YourTableName, YourTableName[Status] <> "S3" )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[harshnathani · Accepted solution]

Hi @Anonymous ,

You can create a new Table.

Table 2 =
VAR a =
    CALCULATETABLE (
        VALUES ( 'Table'[Name] ),
        'Table'[Status] = "S3"
    )
VAR b =
    VALUES ( 'Table'[Name] )
VAR c =
    EXCEPT (
        b,
        a
    )
RETURN
    CALCULATETABLE (
        'Table',
        'Table'[Name]
            IN c
    )

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[harshnathani · Accepted solution]

Hi @Anonymous ,

You can create a new Table.

Table 2 =
VAR a =
    CALCULATETABLE (
        VALUES ( 'Table'[Name] ),
        'Table'[Status] = "S3"
    )
VAR b =
    VALUES ( 'Table'[Name] )
VAR c =
    EXCEPT (
        b,
        a
    )
RETURN
    CALCULATETABLE (
        'Table',
        'Table'[Name]
            IN c
    )

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[parry2k · Accepted solution]

@Anonymous following dax for new table

New Table = 
CALULATETABLE ( YourTableName, YourTableName[Status] <> "S3" )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k ,

This will remove the Status of "S3". However, I want to remove all the names that have the status of "S3". In our example, since Name - A has S3 status, it should not populate at all.

[amitchandak · Accepted solution]

@Anonymous , This might have been resolved already by the last two updates. Posting As I created a file in between. Please find the file after the signature.

[parry2k]

@Anonymous got it, try this

Table 2 = 
VAR __table = CALCULATETABLE ( VALUES ( Data[Name] ), Data[Status] = "S3" )
RETURN CALCULATETABLE ( Data, EXCEPT ( VALUES ( Data[Name] ), __table ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡