Choosing a value based on a report filter


  • 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
    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!



    Julian Wissel | BI for NAV @ | Blog @

    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 @ | Blog @

    Monday, August 18, 2014 9:35 AM