Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
@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 |
123456 | |
123456/123456/123456 | |
123456/99879/34234/234234/234234 | |
786876876 | Uploaded |
DW12789 | |
DW12789 | Uploaded |
TBA | |
TBC |
Result
Purchase Order | Reason | Reason |
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 | |
Uploaded | Error |
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
@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
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
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 | |
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 | |
Pending PO | ||
Uploaded | Error |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |