Forum Discussion
Advance Filtering
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,
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 1Now, 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!
3 Replies
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 1Now, 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!
That's good!
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.
