Return Distinct Value Based on Multiple Rows (non-numeric)

Hi Anonymous ,

Here is my proposed solution for you.

You create a new calculated measure with the following DAX code:

Order-Level Comment Measure = 

VAR isAnyShortage = CALCULATE(
IF( COUNTX('Orders', IF([Part-Level Comment]="SHORTAGE", 1, BLANK() ))>0
   ,TRUE, FALSE 
) , ALLEXCEPT(Orders,Orders[Order Number]))

VAR partLevelCommentsAreEqual = CALCULATE(
IF( DISTINCTCOUNT('Orders'[Part-Level Comment])>1
   ,FALSE, TRUE 
) 
, ALLEXCEPT(Orders,Orders[Order Number]))

VAR highestRankedOption = CALCULATE(
VAR highestRankedNumber = MIN('Orders'[Option Rank])
RETURN LOOKUPVALUE('Options Rank'[Part-Level Comment],'Options Rank'[RANK],highestRankedNumber)
, ALLEXCEPT(Orders,Orders[Order Number]))


RETURN IF(isAnyShortage, "SHORTAGE", highestRankedOption)

The code checks your 3 conditions below and then returns the correct Order-Level Comment based on those conditions. (When I wrote the code I realized that condition 2 is not necessary: when all the part-level comments are the same, condition 3 will give the same result as condition 2)

Here is a screenshot:

And here is a sample Power BI file that you can download.

Let me know if this helps you!

LC

Interested in Power BI and DAX training? Check out my blog at www.finance-bi.com