Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
DessiG
Regular Visitor

Filter out results based on specific text in a column

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"
)
2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@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"
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

v-jianpeng-msft
Community Support
Community Support

Your solution is great parry2k , It worked!!

Hi, @DessiG 

Here is the test dataset I used:

vjianpengmsft_0-1728008484393.png

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

vjianpengmsft_1-1728008525311.png

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

 

 

Best Regards

Jianpeng Li

View solution in original post

6 REPLIES 6
v-jianpeng-msft
Community Support
Community Support

Your solution is great parry2k , It worked!!

Hi, @DessiG 

Here is the test dataset I used:

vjianpengmsft_0-1728008484393.png

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

vjianpengmsft_1-1728008525311.png

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

 

 

Best Regards

Jianpeng Li

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:

DessiG_0-1728043953674.png

 

Calculated column:

DessiG_1-1728044034779.png

 

Result:

DessiG_2-1728044071838.png

 

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
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

parry2k
Super User
Super User

@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"
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.