Forum Discussion
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 |
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
3 Replies
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
Glad this is useful for you!
Do not hesitate to ask if you need more help,
LC