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

[Anonymous]

Hello! I've been struggling with this issue for some time... hoping someone out there knows a trick to help. I deal with orders that have a variety of combinations of parts on an order. I was able to write some DAX to populate a comment (Part-Level Comment below) based on other factors that affect each Part. My issue now, is populating ONE comment per Order Number based on what Part-Level Comments I have on the order. I have seen examples on how this could be accomplished with SUMMARIZE if I were dealing with numeric data... but this is all text. 

Conditions:

• if ANY of the Part-Level Comments on on Order Number is "SHORTAGE", then the whole order gets a "SHORTAGE" Order-Level Comment no matter what other Part-Level Comments exist (example 1 in the table)
• if the Part-Level Comments are all equal, then the Order-Level Comment should equal the Part-Level Comment (example 2 in the table)
• if there are multiple Part-Level Comments (but no "SHORTAGE" Part-Level Comments), return the Part-Level Comment ranking the highest based on another rank table (example 3 in first table)

EXAMPLE	Order Number	Part	Part-Level Comment	Order-Level Comment		RANK	Part-Level Comment
1	11111	A	OPTION 1	SHORTAGE		1	OPTION 1
1	11111	B	OPTION 1	SHORTAGE		2	OPTION 2
1	11111	C	SHORTAGE	SHORTAGE		3	OPTION 3
2	22222	A	OPTION 1	OPTION 1
2	22222	B	OPTION 1	OPTION 1
3	33333	A	OPTION 2	OPTION 2
3	33333	B	OPTION 3	OPTION 2

[lc_finance · Accepted solution]

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

[Anonymous]

Thank you! This is what I was looking for. Much obliged.

[lc_finance]

Glad this is useful for you!

Do not hesitate to ask if you need more help,

LC