Forum Discussion

gauravnarchal's avatar
gauravnarchal
Post Prodigy
4 years ago
Solved

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
Need Help
Show and Tell
  • parry2k's avatar
    parry2k
    4 years ago

    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.⚡

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    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.⚡

    • gauravnarchal's avatar
      gauravnarchal
      Post Prodigy
      4 years ago

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

       

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

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