Filter out results based on specific text in a column

[DessiG]

Hi Everyone, 

I have the following working query, but I need to add a condition for the results to exclude rows that have a status "COMPLETED" in the "Data[Status]" column of the table. I tried with IS NOT, Filter and Containstring, but I do not seem to be approaching this in the correct way. Please can someone lend a hand. Thanks a lot in advance!

Discussion Point =

SWITCH (

    TRUE (),

    Data[Follow-upDate] <= TODAY (), "To Be Discussed",

    ISBLANK ( Data[Follow-upDate]), "To Be Discussed",

    AND ( ISBLANK ( Data[Completion Date] ), ISBLANK ( Data[Follow-upDate] ) ), "To Be Discussed",

    "OK"

)

[parry2k · Accepted solution]

@DessiG if you are adding this as a column, you can do something like this:

Discussion Point =
SWITCH (
    TRUE (),
    Data[Status] = "Completed", "Your status value whatever you want",
    Data[Follow-upDate] <= TODAY (), "To Be Discussed",
    ISBLANK ( Data[Follow-upDate]), "To Be Discussed",
    AND ( ISBLANK ( Data[Completion Date] ), ISBLANK ( Data[Follow-upDate] ) ), "To Be Discussed",
    "OK"
)

[v-jianpeng-msft · Accepted solution]

Your solution is great parry2k , It worked!!

Hi, @DessiG 

Here is the test dataset I used:

Using the expression provided by Super user can get the correct result:

If you have solved the current problem, you can mark the reply that helped you as the solution.

Best Regards

Jianpeng Li

[DessiG]

Thank you very much both @v-jianpeng-msft and @parry2k !

For me the solution did not work because I had one more status and the <= Today () was preventing the "COMPLETED" to execute as I needed. Otherwise, it work as illustrated by @v-jianpeng-msft  here. THANK YOU both very much!

Just as FYI for the community, below is how I solved in my particular case (after a couple of days of triel and error LOL):

Initial statuses:

Calculated column:

Result:

It seemed counter intuitive to me, but it gives me the results I need, so this is what I took, but will accept both your answers as well as they too work in a bit of a dirrectedn context.

THANSK a lot again!

[parry2k]

@DessiG the expression I gave you is the solution, not sure if you have tried it or not, if that is to what you are looking for then read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[DessiG]

I am sorry, but on my end I do not see any expression - could you please re-post. Thank you

[parry2k · Accepted solution]

@DessiG if you are adding this as a column, you can do something like this:

Discussion Point =
SWITCH (
    TRUE (),
    Data[Status] = "Completed", "Your status value whatever you want",
    Data[Follow-upDate] <= TODAY (), "To Be Discussed",
    ISBLANK ( Data[Follow-upDate]), "To Be Discussed",
    AND ( ISBLANK ( Data[Completion Date] ), ISBLANK ( Data[Follow-upDate] ) ), "To Be Discussed",
    "OK"
)

[DessiG]

Thank you for the additional resource, but are you able to please help with the issue at hand, please? Thank you