Forum Discussion
Creating a complicated filter using DAX
Good afternoon I have a bit of a complicated filter I'm hoping someone could help me with I have a data set that I'm trying to filter data down to that could be a bit complex, but want to show in a table card. I'll provide generic data of what I'm looking for. My thought process is I want to create a column (Column 3 below) for each data field if it meets a certain criteria with a "yes" or "no" in the column. This filter involves first a few columns of data. I'll try to put in words what I want the filter today.
For Column 1, Exclude "ABC" and "123" - Main Filter
(Here's where it gets tricky)
For Column 2, There is some data in this column ("Test 2" and "Test 4") that I need to include with Column 1, even if the corresponding value in column 1 for this is "ABC" and "123"
Also for Column 2, there are some values that meet the criteria for column 1 (aren't "ABC" or "123", but I want those filtered out such as "XYZ" and "789"
Sample Columns Below
| Column 1 (ID) | Column 2 (Name) | Column 3 (Meets Criteria "yes" or "no") |
| 111 | Test 1 | Yes |
ABC | Test 2 | Yes |
| AAA | Test 3 | Yes |
| ABC | Test 6 | No |
| BBB | XYZ | No |
| 123 | Test 4 | Yes |
| 123 | Test 5 | No |
| BBB | 789 | No |
| ABC | Test 7 | No |
- Meets Criteria = if((not [ID] in {"ABC","123"} || [Name] in {"Test 2","Test 4"}) && not [Name] in {"XYZ","789"},"Yes","No")Note: Your "Test 2" string has an extra space.
6 Replies
Let’s break down your filtering criteria step by step for clarity:
- Column 1 (ID) Exclusions: Exclude entries that have "ABC" or "123".
- Column 2 (Name) Inclusions: Include "Test 2" and "Test 4" even if they’re paired with "ABC" or "123".
- Column 2 (Name) Exclusions: Exclude "XYZ" and "789" even if their corresponding ID in Column 1 meets the criteria.
Based on your sample, here's how Column 3 would look:
Column 1 (ID) Column 2 (Name) Column 3 (Meets Criteria) 111 Test 1 Yes ABC Test 2 Yes AAA Test 3 Yes ABC Test 6 No BBB XYZ No 123 Test 4 Yes 123 Test 5 No BBB No 789 No ABC Test 7 No Summary for Column 3:
- "Yes" if:
- ID is not "ABC" or "123", or
- Name is "Test 2" or "Test 4".
- "No" if:
- ID is "ABC" or "123" without the specific Names.
Let me know if you need further adjustments or clarifications!
Yes, that is the correct logic
I realized I left a small piece out. The data for "ID" is coming from one table of data while "Name" is coming from another table.
- Meets Criteria = if((not [ID] in {"ABC","123"} || [Name] in {"Test 2","Test 4"}) && not [Name] in {"XYZ","789"},"Yes","No")Note: Your "Test 2" string has an extra space.
I realized I left a small piece out. The data for "ID" is coming from one table of data while "Name" is coming from another table.
That logic is correct
