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

Need help with measure

Hello - I need help in creating a measure to return:-

 

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

If Purchase Order has an “All Digit Value” and Reason is “Blank” Return “Upload in Progress”

If Purchase Order has an “All Digit Value” and Reason is “Uploaded” Return “Uploaded”

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

else

Return "Error"

 

Purchase OrderReason
Email 
123456 
786876876Uploaded
DW12789 
DW12789Uploaded
TBA 
TBC 

 

Result

 

Purchase OrderReasonResult
Email Pending PO
123456 Upload in Progress
786876876UploadedUploaded
DW12789 Uploaded
DW12789UploadedUploaded
TBA Error
TBC Error

 

6 REPLIES 6
TomMartens
Super User
Super User

Hey @gauravnarchal ,

 

I used this DAX statement to create a calculated column. I recommend to use a calculated column an not a measure, as this will allow to use the column as a slicer and, most of the time it's not a good idea to create a measure that returns a non-numeric scalar:

 

result = 
var __PurchaseOrder = 'Table'[Purchase Order]
var __Reason = 'Table'[Reason]
return
IF(
    ( __PurchaseOrder IN { "Email" , "TBA" , "TBC" } && ( ISBLANK( __Reason ) || __Reason = "" ) ) , "Pending Order" 
    , IF(
        ( NOT( ISERROR( VALUE( __PurchaseOrder ) ) )  && ( ISBLANK( __Reason ) || __Reason = "" ) ) , "Upload in Progress"
        , IF(
            ( NOT( ISERROR( VALUE( __PurchaseOrder ) ) ) && __Reason = "Uploaded" ) , "Uploaded"
            , IF( LEFT( __PurchaseOrder , 2 ) = "DW" , "Uploaded"
            , "Error"
            )
        )
    )
)

 

This result looks like this:

image.png

Hopefully, this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens  - I will create the calculated column as advised, but I need to slightly change the requirement and would need your help.

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

If Purchase Order has an “All Digit Value” and Reason is “Blank” Return “Upload in Progress”

If Purchase Order has an “All Digit Value” and Reason is “Uploaded” Return “Uploaded”

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

else

Return "Error"

 

Purchase OrderReason
Email 
123456 
123456/123456/123456 
123456/99879/34234/234234/234234 
786876876Uploaded
DW12789 
DW12789Uploaded
TBA 
TBC 

 

Result

 

Purchase OrderReasonReason
Email Pending PO
123456 Upload in progress
123456/123456/123456 Upload in progress
123456/99879/34234/234234/234234UploadedUploaded
989879879/672686/57657/4757657UploadedUploaded
786876876UploadedUploaded
DW12789 Uploaded
DW12789UploadedUploaded
TBA Pending PO
TBC Pending PO
 UploadedError
EmailUploadedError

 

Hey @gauravnarchal ,

 

i updated my initial answer, now I'm using the IN operator in combination with a table constructed by using curly braces.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens  - When I am having a purchase order number that includes slash "/" it is still giving me the error. For eg Purchase order number 123456/123456/123456 is all digits except slash

 

gauravnarchal_0-1631284415499.png

 

Hey @gauravnarchal ,

 

as the string that contains the /-character is not a value, I consider the result of the DAX statement correct.

If you need more help, e.g. on how to REPLACE or SUBSTITUTE the / with "", think again, and provide a pbix file that contains all the possible variations of strings that might appear in your data using the manual input method. Upload the file to onedrive or dropbox and share the link. If you are using Excel to create the pbix file share the xlsx as well.

Describe all business rules that have to be applied based on the sample data you provide.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens  - Please find below the data table and result table.

 

Excel file Link - Click Here

 

Data

 

PurchaseOrder                             Reason

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

 

PurchaseOrder                          Reason        Status

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

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

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!