Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |