DAX Help

[S_M_]

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,

[S_M_]

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

[ryan_mayu · Accepted solution]

@S_M_ 

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])

[ryan_mayu]

@S_M_ 

here is a workaround for you.

Measure = IF(CALCULATE(MAX('Table'[ProjectID]),ALLEXCEPT('Table','Table'[Vendor_ID],'Table'[Product]))=MAX('Table'[ProjectID]),1,0)

then set up conditional formatting to change the color

pls see the attachment below