Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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? 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |