cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gauravnarchal
Post Partisan
Post Partisan

Calculated Column

Hello - I need help in creating a calculated column to return:-

 

If Purchase Order = “Email” or “TBA” or “TBC” and Reason is “Blank” Return “Pending PO”

If Purchase Order has one digit value  and Reason is “Blank” Return “Upload in Progress”

If Purchase Order has one digit value  and Reason is “Uploaded” Return “Uploaded”

If Purchase Order has a “Value starting with DW” Return “Uploaded”

else

Return "Error"

 

(Data) Excel file Link - Click Here

 

PurchaseOrderReason
1063138/1064662Uploaded
1063507/1063393 
8007215/8006322Uploaded
8006386/8007591Uploaded
TBA 
TBAUploaded
1064132/1064116Uploaded
8006465/8006468/8006467 
1063507/1063393/80064876Uploaded
TBAUploaded
TBC 
1063138Uploaded
1064662 
EMAIL 
EMAILUploaded

 

Result

 

PurchaseOrderReasonStatus (Result)
1063138/1064662UploadedUploaded
1063507/1063393 Upload in Progress
8007215/8006322UploadedUploaded
8006386/8007591UploadedUploaded
TBA Pending PO
TBAUploadedError
1064132/1064116UploadedUploaded
8006465/8006468/8006467 Upload in Progress
1063507/1063393/80064876UploadedUploaded
TBAUploadedError
TBC Pending PO
1063138UploadedUploaded
1064662 Upload in Progress
EMAIL Pending PO
EMAILUploadedError
1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@gauravnarchal add a new column using following DAX and it will do it, you can always tweak it as you see fit:

 

Status = 
VAR __PO = PO[PurchaseOrder]
VAR __Reason = COALESCE ( PO[Reason], "" )
VAR __isNumber = NOT ( IFERROR ( VALUE ( SUBSTITUTE ( PO[PurchaseOrder], "/", "" ) ), BLANK() ) == BLANK() )
RETURN
SWITCH ( TRUE(),
    __PO IN { "Email", "TBA", "TBC" } && __Reason = "", "Pending PO",
    __isNumber && __Reason = "", "Uploaded in Progress",
    ( __isNumber && __Reason = "Uploaded") || ( LEFT ( __PO, 2 ) = "DW" ), "Uploaded",
    "Error"
)

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






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

4 REPLIES 4
parry2k
Super User
Super User

@gauravnarchal add a new column using following DAX and it will do it, you can always tweak it as you see fit:

 

Status = 
VAR __PO = PO[PurchaseOrder]
VAR __Reason = COALESCE ( PO[Reason], "" )
VAR __isNumber = NOT ( IFERROR ( VALUE ( SUBSTITUTE ( PO[PurchaseOrder], "/", "" ) ), BLANK() ) == BLANK() )
RETURN
SWITCH ( TRUE(),
    __PO IN { "Email", "TBA", "TBC" } && __Reason = "", "Pending PO",
    __isNumber && __Reason = "", "Uploaded in Progress",
    ( __isNumber && __Reason = "Uploaded") || ( LEFT ( __PO, 2 ) = "DW" ), "Uploaded",
    "Error"
)

 

Follow us on LinkedIn

 

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






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

Ashish_Mathur
Super User
Super User

Hi,

I think i can solve this using Power Query.  Would you be OK with a Power Query solution?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User
Super User

@gauravnarchal what you mean by this condition, can you clarify:

 

If Purchase Order has one digit value  






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.





Hi @parry2k  - I mean all values in purchase order should be digits except "/" slash.

 

For eg:-  1063138/1064662 or 1063507/1063393/80064876 or 1063138

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!