Forum Discussion
DAX Help
I am trying to get the highlighted final project risk rating for each Vendor and Product service based on maximum project ID. (highlighted rows).
I have below calculation but I get error on using Max(). Project_ID_Calc is a calculated field I created by removing the String from Project ID and converting it to whole number.
Flag = CALCULATE(MIN('Table1'[FINAL_PROJECT_RISK_RATING]), ALLEXCEPT('Table1','Table1'[VENDOR_ID],'Table1'[PRODUCT_SERVICE]),'Table1'[PROJECT_ID] = MAX('Table1'[PROJECT_ID_CALC]) )
Any help is much appreciated.
Thanks,
you can create a column
Column = VAR _max=CALCULATE(max('Table'[PROJECT_ID]),ALLEXCEPT('Table','Table'[VENDOR_ID],'Table'[PRODUCT_SERVICE])) return maxx(FILTER('Table','Table'[VENDOR_ID]=EARLIER('Table'[VENDOR_ID])&&'Table'[PROJECT_ID]=_max),'Table'[FINAL_PROJECT_RISK_RATING])or a measure
measure = VAR _max=CALCULATE(max('Table'[PROJECT_ID]),ALLEXCEPT('Table','Table'[VENDOR_ID],'Table'[PRODUCT_SERVICE])) return maxx(FILTER(all('Table'),'Table'[VENDOR_ID]=max('Table'[VENDOR_ID])&&'Table'[PROJECT_ID]=_max),'Table'[FINAL_PROJECT_RISK_RATING])
3 Replies
Thanks, this was helpful, however, I would like to get the final project risk rating for that condition. I am trying to get this flag:
Again, any help is much appreciated.
VENDOR_ID PRODUCT_SERVICE PROJECT_ID FINAL_PROJECT_RISK_RATING Flag Vend-0031 ABC TP-234 Above level Above level Vend-0031 ABC TP-235 Needs Improvement Above level Vend-0031 ABC TP-236 Needs Improvement Above level Vend-0031 ABC TP-237 Needs Improvement Above level Vend-0031 ABC TP-238 Above level Above level Vend-0031 DEF TP-1245 Needs Improvement Above level Vend-0031 DEF TP-1256 Above level Above level Vend-0031 DEF TP-1247 Needs Improvement Above level Vend-0031 DEF TP-1248 Above level Above level you can create a column
Column = VAR _max=CALCULATE(max('Table'[PROJECT_ID]),ALLEXCEPT('Table','Table'[VENDOR_ID],'Table'[PRODUCT_SERVICE])) return maxx(FILTER('Table','Table'[VENDOR_ID]=EARLIER('Table'[VENDOR_ID])&&'Table'[PROJECT_ID]=_max),'Table'[FINAL_PROJECT_RISK_RATING])or a measure
measure = VAR _max=CALCULATE(max('Table'[PROJECT_ID]),ALLEXCEPT('Table','Table'[VENDOR_ID],'Table'[PRODUCT_SERVICE])) return maxx(FILTER(all('Table'),'Table'[VENDOR_ID]=max('Table'[VENDOR_ID])&&'Table'[PROJECT_ID]=_max),'Table'[FINAL_PROJECT_RISK_RATING])
