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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gauravnarchal
Post Prodigy
Post Prodigy

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.



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

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.



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.

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  



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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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