cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cchamb Frequent Visitor
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)

 

EXAMPLEOrder NumberPartPart-Level CommentOrder-Level Comment RANKPart-Level Comment
111111AOPTION 1SHORTAGE 1OPTION 1
111111BOPTION 1SHORTAGE 2OPTION 2
111111CSHORTAGESHORTAGE 3OPTION 3
222222AOPTION 1OPTION 1   
222222BOPTION 1OPTION 1   
333333AOPTION 2OPTION 2   
333333BOPTION 3OPTION 2   
1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
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]))

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:

 

Image 2019-10-21 at 6.54.46 PM.png

 

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

 

View solution in original post

3 REPLIES 3
Super User II
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]))

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:

 

Image 2019-10-21 at 6.54.46 PM.png

 

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

 

View solution in original post

cchamb Frequent Visitor
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
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

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors