Creating a dimension based on measures

Question

Greetings,&nbsp;I currently have a list of products with associated measures - see example below:&nbsp;Product &nbsp; &nbsp; &nbsp;&nbsp; Actual &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Budget &nbsp; &nbsp; &nbsp;&nbsp; Variance &nbsp; &nbsp; &nbsp; &nbsp; Abs VarianceA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 90 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; +10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 200 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 250 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; (50) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50C &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 150 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 175 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; (25) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 25&nbsp;I created a measure that allows me to aggregate the products based on the size of variance (selected in a separate slicer).&nbsp; Based on that selection, I want to create a new dimension of the product names.&nbsp; For example, if a project has a absolute value variance &gt; 30, what I'd like to see is:&nbsp;&nbsp;Product &nbsp; &nbsp; &nbsp;&nbsp; Actual &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Budget &nbsp; &nbsp; &nbsp;&nbsp; Variance &nbsp; &nbsp; &nbsp; Abs Variance &nbsp; &nbsp;&nbsp; Revised_Product NameA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 90 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; +10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; All OtherB &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 200 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 250 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; (50) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 50 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BC &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 150 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 175 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; (25) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 25 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; All Other&nbsp;I am not sure why the following syntax doesn't work.&nbsp; When I write the formula, I am not able to "call up" the column of product names:&nbsp;Revised_Product Name = SWITCH (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; TRUE( ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; [Abs Variance] &lt;= [Selected Variance Size], "All Other",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 'Table'Product&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; )&nbsp;Thanks for any insights.

anonymous · Accepted Answer

Revised_Product Name =
IF( HASONEFILTER( Table[Product] ),
	IF(
	    [Abs Variance] &lt;= [Selected Variance Size],
	    "All Other",
	    SELECTEDVALUE( Table[Product] )
	)
)
Best
D

Forum Discussion

Creating a dimension based on measures

2 Replies

Related content

Unable to create Date Dimension and Date related measures

Need help creating a measure

Help to create DAX Measure

degenerate dimensions

Creating histogram using measure and dimension from data warehouse