# Choosing a value based on a report filter

### Question

• I need a bit of help improving a calculation in my model.

Its a basic model, Orders FactTable, Items Dimension & Locaiton Dimension.
For all locations the stdPrice is used, except for Location 3, where special price is used.

How do i work this into my calculation to account for location 3? (i really need it to be a measure, not a calculated column. so far my measure is:

cost:=calculate(sumx(Table1,[quantity]* RELATED(items[PriceA])))

Sample data belo. Thanks for any help, its really appreciated.

```--Items Table

ItemID	ItemName	StdPrice	SpecialPrice
1		Car			10		20
2      	Bus			50		100

--Fact Table
OrderID	ItemID	LocationID	Quantity
1		1		1			2
2		2		1			5
3		2		2			5
4		3		3			2

--Location
LocationID	LocationName
1			loc A
2			Loc B
3			Loc C```

Saturday, August 16, 2014 9:56 AM

### All replies

• Hi Winston,

You could split that measure into two parts and create a measure that combines both:

coststandard:=calculate(sumx(filter(Tabelle1;Tabelle1[Location ID]<>3);[Quantity]*related('Item'[stdprice])))

costspecial:=calculate(sumx(filter(Tabelle1;Tabelle1[Location ID]=3);[Quantity]*related('Item'[special price])))

cost := coststandard+costspecial

Alternatively if query performance is a problem you could add a calculated column to your fact table that makes the cost-calculation for each fact row during refresh time what would avoid that sumx/filter-combination at "runtime".

I hope that helps!

Regards,

Julian

Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

Saturday, August 16, 2014 5:36 PM
• Hey Julian.

Thanks for the response! The calculations given appear to be fairly syntactically incorrect, and as the problem i am having here is syntax, i still have my issue. Im playing around with the concept you describe though to see if i can get it working. thanks!

Monday, August 18, 2014 8:25 AM
• Try to replace ; with ,

Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

Monday, August 18, 2014 9:35 AM