Advance Filtering

[AbhinavJoshi]

Hi Everyone, 

I have the following dataset, and I would like to remove the rows where the Type is Child & Spouse and the Value is Single. 

ID	Type	Attribute	Value
22	Member	SEC	Family
48	Member	SEC	Single
48	Child	SEC	Single
48	Spouse	SEC	Single
51	Member	EH	Family
51	Child	EH	Family
51	Spouse	EH	Family
55	Member	EH	Single

Thanks,

[v-yilong-msft]

Hi @AbhinavJoshi ,

I create a table as you mentioned.

Then I create a new table and here is the DAX code.

Table 2 =
FILTER (
    'Table',
    NOT ( ( 'Table'[Type] = "Child"
        || 'Table'[Type] = "Spouse" )
        && 'Table'[Value] = "Single" )
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bipin-Lala · Accepted solution]

Hi @AbhinavJoshi,

You can create a custom column in Query Editor, let's say "FilterRows" and assign a value to it (like 0) if it matches your conditions for filtering out the rows.

if ([Type] = "Child" or [Type] = "Spouse") and [Value] = "Single" 
then 0 
else 1

Now, FilterRows = 0 for all rows that match your conditions, otherwise it's 1

In the next step, you can filter out all rows where FilterRows = 0

This should filter your table as per your requirements. Let me know if you have any other requirements!

[Danilo_Matos]

That's good!