Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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 |
Solved! Go to 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
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
Thank you! This is what I was looking for. Much obliged.
Glad this is useful for you!
Do not hesitate to ask if you need more help,
LC
User | Count |
---|---|
102 | |
84 | |
77 | |
70 | |
67 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |