Forum Discussion

PatrykRoz's avatar
PatrykRoz
New Member
5 years ago

Calculating other values within same visual table based on given criteria

Dear All, I am looking for DAX Measure which could help me with below issue. My visual table contains information about fabric id, product code, expiration date for that product, stock quantity...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

 

[Suggestion] = // measure
IF ( ISINSCOPE( T[Fabric_Code] ),
	var CurrentFabric = SELECTEDVALUE( T[Fabric_Code] )
	var CurrentSalesValue = [Sales Value]
	var CurrentKey =
		SUMMARIZE(
			T,
			T[EAN_Code],
			T[Expiration_Date]
		)
	var CurrentKeyIsSingle =
		COUNTROWS( CurrentKey ) = 1
	var Result =
		IF( CurrentKeyIsSingle,
			var BetterFabric = 
				MAXX(
					CALCULATETABLE(
						TOPN(1,
							FILTER(
								DISTINCT( T[Fabric_Code] ),
								[Sales Value] > CurrentSalesValue
							),
							[Sales Value],
							DESC
						),
						ALLSELECTED( T[Fabric_Code] )
					),
					T[Fabric_Code]
				)
			return
				BetterFabric
		)
	return
		Result
)