I have been replicating a retired excel KPI pack within Power BI and have come across something I need help with.
The KPI pack relates to shipping and includes delays in the door to door shipping process broken out into the following elements.
1) Days Late at Origin Delivery
2) Days Late During Origin Consolidation
3) Days Late During Vessel at Sea
4) Days Late During Delivery to DC
In the dataset, I have some calculations which determines where the biggest delay is happening and I need to return the description as listed above for those delays.
I have created a DAX measure which does this for me as follows...
Maximum Delay Reason =
VAR MaxPrice =
MAX( 'Grouped: PO_Level (OINDC Delay Analysis)'[Value] )
RETURN
CALCULATE (
SELECTEDVALUE ( 'Grouped: PO_Level (OINDC Delay Analysis)'[Delay Reason] ),
'Grouped: PO_Level (OINDC Delay Analysis)'[Value] = MaxPrice
)
This works, except I have an issue if the biggest delay is the same for 2 of the reasons...example as follows...
Days Late at Origin Delivery = 4 days
Days Late During Origin Consolidation = 2 days
Days Late During Vessel at Sea = 4 days
Days Late During Delivery to DC = 1 day
In this case, PowerBi does not know how to handle the fact there are 2 highest reasons and returns a blank instead.
Any ideas on how I can fix this? I'm happy for any method to be used to decide on a reason code (sort alphabetically maybe) as long as I get a reason instead of a blank.
Many thanks in advance
