Forum Discussion
Filter out rows based on two columns (in DAX)
Dear Microsoft PowerBI community,
I have been a long time user of this forum and I really appreciate all the users that raised their queries and the those that respond to them.
I have come across an issue that seems easy but I can't figure it out.
I have a table that looks like the below in the query editor.
| Package | Planned/Actual | Milestone A | Milestone B |
| A | Planned | 12/08/2021 | 11/09/2021 |
| A | Actual | 12/08/2021 | null |
| B | Planned | 13/08/2021 | 11/09/2021 |
| B | Actual | null | 14/08/2021 |
| C | Planned | 10/09/2021 | 12/08/2021 |
| C | Actual | 10/09/2021 | 13/08/2021 |
I have created a conditional column in the query editor to say yes if the date is in the last 4 weeks from today.
| Package | Planned/Actual | Milestone A | Check A | Milestone B | Check B |
| A | Planned | 12/08/2021 | No | 11/09/2021 | No |
| A | Actual | 12/08/2021 | No | null | No |
| B | Planned | 13/08/2021 | Yes | 11/09/2021 | No |
| B | Actual | null | No | 14/08/2021 | Yes |
| C | Planned | 10/09/2021 | Yes | 12/08/2021 | No |
| C | Actual | 10/09/2021 | Yes | 13/08/2021 | Yes |
I am using this conditional column to filter the dates that are depicted in two separate tables. If yes, then show the dates.
The resultant first table is this:
| Package | Planned/Actual | Milestone A | Check A |
| B | Planned | 13/08/2021 | Yes |
| C | Planned | 10/09/2021 | Yes |
| C | Actual | 10/09/2021 | Yes |
I only want to show the actual date if there's both a planned and an actual date and I want to keep the planned date if there is only a planned date. So I am looking for the following result:
| Package | Planned/Actual | Milestone A | Check A |
| B | Planned | 13/08/2021 | Yes |
| C | Actual | 10/09/2021 | Yes |
Is there a dax query that can help me apply this filter?
Any guidance would be appreciated.
Kind regards,
GSPBI
Hi, GSPBI
You can create a measure and use it in filter pane to filter the row you want to show in table.
Since you have already written Check A, I will directly use column instead. My idea is to divide into two different situations.
Measure:
Measure = VAR a = COUNTX ( FILTER ( ALL ( 'Table' ), [Check A] = "Yes" && [Package] = SELECTEDVALUE ( 'Table'[Package] ) ), [Package] ) VAR b = IF ( MAX ( 'Table'[Planned/Actual] ) = "Actual" && a = 2, 1, IF ( MAX ( 'Table'[Check A] ) = "Yes" && a = 1, 1 ) ) RETURN bDid I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,Community Support Team _ Janey
3 Replies
- v-janeyg-msft
Community Support
Hi, GSPBI
You can create a measure and use it in filter pane to filter the row you want to show in table.
Since you have already written Check A, I will directly use column instead. My idea is to divide into two different situations.
Measure:
Measure = VAR a = COUNTX ( FILTER ( ALL ( 'Table' ), [Check A] = "Yes" && [Package] = SELECTEDVALUE ( 'Table'[Package] ) ), [Package] ) VAR b = IF ( MAX ( 'Table'[Planned/Actual] ) = "Actual" && a = 2, 1, IF ( MAX ( 'Table'[Check A] ) = "Yes" && a = 1, 1 ) ) RETURN bDid I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,Community Support Team _ Janey
v-janeyg-msft Mohammad_Refaei Thank you very much for your help, I really appreciate it. Worked like a charm.
- Mohammad_Refaei
Solution Specialist
You can keep working with the Query Editor.
1. Sort Ascending both the date and the Planned/Actual fields.
Table.Sort(#"Changed Type",{{"Milestone A", Order.Ascending}, {"Planned/Actual", Order.Ascending}})2. Wrap the last sorting step by Table.Buffer function
= Table.Buffer(Table.Sort(#"Changed Type",{{"Milestone A", Order.Ascending}, {"Planned/Actual", Order.Ascending}}))3. Remove Duplicates for all columns except Planned/Actual
= Table.Distinct(#"Sorted Rows", {"Package", "Milestone A", "Check A"})This will give you whath you are looking for
