Forum Discussion

AntWickenden's avatar
AntWickenden
Frequent Visitor
3 years ago

Selecting highest result from 4 values and displaying a description for that.

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

 

Need Help

2 Replies