cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User II

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

Hi @cchamb ,

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]))

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

3 REPLIES 3
Super User II

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

Hi @cchamb ,

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]))

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

Frequent Visitor

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

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

Super User II

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

Glad this is useful for you!

Do not hesitate to ask if you need more help,

LC

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!