Forum Discussion
Post ProdigyNeed 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 Order | Reason |
| 123456 | |
| 786876876 | Uploaded |
| DW12789 | |
| DW12789 | Uploaded |
| TBA | |
| TBC |
Result
| Purchase Order | Reason | Result |
| Pending PO | ||
| 123456 | Upload in Progress | |
| 786876876 | Uploaded | Uploaded |
| DW12789 | Uploaded | |
| DW12789 | Uploaded | Uploaded |
| TBA | Error | |
| TBC | Error |
6 Replies
- TomMartens
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:
Hopefully, this provides what you are looking for.
Regards,
Tom
gauravnarchalTomMartens - 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
