Need help with measure

[gauravnarchal]

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 Order	Reason
Email
123456
786876876	Uploaded
DW12789
DW12789	Uploaded
TBA
TBC

Result

Purchase Order	Reason	Result
Email		Pending PO
123456		Upload in Progress
786876876	Uploaded	Uploaded
DW12789		Uploaded
DW12789	Uploaded	Uploaded
TBA		Error
TBC		Error

[TomMartens]

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:

Hopefully, this provides what you are looking for.

Regards,

Tom

[gauravnarchal]

@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 Order	Reason
Email
123456
123456/123456/123456
123456/99879/34234/234234/234234
786876876	Uploaded
DW12789
DW12789	Uploaded
TBA
TBC

Result

Purchase Order	Reason	Reason
Email		Pending PO
123456		Upload in progress
123456/123456/123456		Upload in progress
123456/99879/34234/234234/234234	Uploaded	Uploaded
989879879/672686/57657/4757657	Uploaded	Uploaded
786876876	Uploaded	Uploaded
DW12789		Uploaded
DW12789	Uploaded	Uploaded
TBA		Pending PO
TBC		Pending PO
	Uploaded	Error
Email	Uploaded	Error

[TomMartens]

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

[gauravnarchal]

@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

[TomMartens]

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

[gauravnarchal]

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

Excel file Link - Click Here

Data

PurchaseOrder                             Reason

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

Result

PurchaseOrder                          Reason        Status

1063138/1064662	Uploaded	Uploaded
1063507/1063393		Upload in Progress
8007215/8006322	Uploaded	Uploaded
8006386/8007591	Uploaded	Uploaded
TBA		Pending PO
TBA	Uploaded	Error
1064132/1064116	Uploaded	Uploaded
8006465/8006468/8006467		Upload in Progress
1063507/1063393/80064876	Uploaded	Uploaded
TBA	Uploaded	Error
TBC		Pending PO
1063138	Uploaded	Uploaded
1064662		Upload in Progress
EMAIL		Pending PO
EMAIL	Uploaded	Error