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 created a scoring model and everything worked out just fine. Until I realised I might have to score my variables differently. The score should be set on "AppNo"-level. Meaning the AmountDocs should be scored internally within each AppNo.
Today:
I have a table containing following information. If all ProductAppNo has different values in the AmountDocs field theres not a problem. It means that following result would occur:
ID | ID2 | AmountApproved | ScoringVariable |
2005 | 200501 | 1000 | 1 |
2005 | 200502 | 2000 | 2 |
2005 | 200503 | 3000 | 3 |
2005 | 200504 | 4000 | 4 |
2005 | 200505 | 5000 | 5 |
2005 | 200506 | 6000 | 6 |
2005 | 200507 | 7000 | 7 |
2005 | 200508 | 8000 | 8 |
2005 | 200509 | 10000 | 9 |
ALTHOUGH! When a TIE occurs, I receive following result:
AppNo | ProductAppNo | AmountDocs | ScoringVariable (calculated column) |
2005 | 200501 | 10000 | 2 |
2005 | 200502 | 10000 | 2 |
2005 | 200503 | 10000 | 2 |
2005 | 200504 | 9000 | 1 |
2005 | 200505 | 15000 | 3 |
2005 | 200506 | 10000 | 2 |
2005 | 200507 | 10000 | 2 |
2005 | 200508 | 10000 | 2 |
2005 | 200509 | 10000 | 2 |
DAX used to calculate the ScoringVariable = =RANKX(FILTER(DF;[AppNo]=EARLIER([AppNo]));[AmountDocs];;DESC;Dense)
Wanted result!:
ID | ID2 | AmountApproved | ScoringVariable | Calculation |
2005 | 200501 | 10000 | 4 | TIE between (1+2+3+4+5+6+7) / 7 |
2005 | 200502 | 10000 | 4 | TIE between (1+2+3+4+5+6+7) / 7 |
2005 | 200503 | 10000 | 4 | TIE between (1+2+3+4+5+6+7) / 7 |
2005 | 200504 | 14000 | 8 | Distinct place 8 |
2005 | 200505 | 15000 | 9 | Distinct place 9 |
2005 | 200506 | 10000 | 4 | TIE between (1+2+3+4+5+6+7) / 7 |
2005 | 200507 | 10000 | 4 | TIE between (1+2+3+4+5+6+7) / 7 |
2005 | 200508 | 10000 | 4 | TIE between (1+2+3+4+5+6+7) / 7 |
2005 | 200509 | 10000 | 4 | TIE between (1+2+3+4+5+6+7) / 7 |
Need help with how I should write my DAX-calculated column to achieve above result in the "ScoringVariable" according to the calculation mentioned in the "Calculation" column. Thank you very much for your help!
CJ
Solved it myself.
Hi @CJMolin,
Glad to hear that you have solved it by yourself. If possible, would you please kindly share your solution and mark it as an answer so that it can benefit more users having similar problem?
Regards,
Yuliana Gu
Any experts out there? 🙂
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |