Return yes when all rows are filled

[Rochejf]

Hi guys, I'm wondering whether this can be done in Powerbi?

Referring to the table below.

I have 3 sales orders each separate into different sales tasks. What I wish to do now is, when all sales tasks are fulfilled with a date filled in the sales date column, the completion will turn into yes. If none or not all sales task is not fulfilled, the completion will stay as No.

Let me know if such logic can be performed in Powerbi. Thanks!

Sales Order	Sales Task	Sales Date	Completion
SO111	SO111/1	28-Jul-23	No
SO111	SO111/2		No
SO222	SO222/1	28-Jul-23	Yes
SO222	SO222/2	28-Jul-23	Yes
SO333	SO333/1	28-Jul-23	No
SO333	SO333/2		No
SO333	SO333/3	28-Jul-23	No

[ryan_mayu · Accepted solution]

@Rochejf 

you can create a new column

Column = if(CALCULATE(count('Table (2)'[Sales Date]),ALLEXCEPT('Table (2)','Table (2)'[Sales Order]))=CALCULATE(count('Table (2)'[Sales Task]),ALLEXCEPT('Table (2)','Table (2)'[Sales Order])),"Y","N")

[ryan_mayu · Accepted solution]

@Rochejf 

you can create a new column

Column = if(CALCULATE(count('Table (2)'[Sales Date]),ALLEXCEPT('Table (2)','Table (2)'[Sales Order]))=CALCULATE(count('Table (2)'[Sales Task]),ALLEXCEPT('Table (2)','Table (2)'[Sales Order])),"Y","N")

[Rochejf]

OMG, this actually works!!! Really appreciate your help!!!!!! 

[ryan_mayu]

you are welcome

[Rochejf]

One more question, if i want to perform this in power query format is it possible?

[ryan_mayu · Accepted solution]

here is a workaround for you.

[MaryAmbrose]

First, thank you and potentially anyone else who reads this.  I guess you misunderstood my post.  I'm not looking for a solution.  As I mentioned in my post...I already know how to do this with DAX, my point was that you shouldn't have to.  And if I miss posted in this community then that is my fault.  I was trying to post in the community where Microsoft sees voted on posted to attempt to fix this type of filter need more permanently.  I don't want to use DAX because that means I must create the DAX for every filter that I want to behave this way.  I would rather have Microsoft add it as a permanent filter capability much like Contains, Does Not Contain, Between, etc.  First By Default allows you to set a listed field (e.g. Orders) by default to a numerically or alphabetically first value.  But thank you for trying to help me solve the issue although a code solution is not the type of solution I was hoping for.