Creating a new table based on multiple criteria

[dandywong]

I am trying to create a new table based on 2 filter criteria:

1) The ScopeStatus has to = Green

2) ProjectPhase has to = Completed

If the 2 criteria are not met, then it will not show up on the new table. Any help is greatly appreciated! Thanks!

[MFelix]

Hi @dandywong,

If you are on the query Editor you just need to Reference the query to the initial one and then add a filter on the columns you need.

On dax add a new table with the following code:

Table =
FILTER (
    ALL ( Projects[Project1]; Projects[ProjectPhase]; Projects[ScopeStatus] );
    Projects[ProjectPhase] = "Completed"
        && Projects[ScopeStatus] = "Green"
)

On the ALL sintax add all the columns you want to pass to the new column from the previous one.

Regards,

MFelix

[dandywong]

Hi there,

I tried this formula, but it didn't work:

Any idea on how to fix this?

[MFelix]

Hi @dandywong,

You need to take out the comma after "Green". The && cannot be preceded by a comma.

Regards,

MFelix

[dandywong]

Thanks MFelix!!!!

Sorry, I think I'm missing something....

Still getting an error.

[MFelix · Accepted solution]

Hi @dandywong,


Your parentesis are misplaced so the all function is.working incorrectly.

The measure should be

Test Table =
FILTER (
    ALL (
        Projects[ProjectIdentifier],
        Projects[ProjectPhase],
        Projects[EnterpriseProjectTypeDescription],
        Projects[ScopeStatus]
    ),
    Projects[ProjectPhase] = "Completed"
        && Projects[ScopeStatus] = "Green"
)

I also notice that you were missing the Project ScopeStatus on your ALL formula without it the filter wouldn't had context and the result would be incorrect.

Regards
MFelix

[dandywong]

Thank you MFelix! That did the trick!