Search code examples
sqlsql-serverssasmdx

SSAS Measure based on filter

I have a Fact table with Order numbers but multiple order lines, so each order number is duplicated Table Design.

I have a cube with multiple measure such as, Order Count(distinct count of order numbers), Order Amount (Sum of order lines) & many more. We have been asked to create a measure that shows any order value under £1000, I went about doing this within the ETL and creating a flag by each order line, then summing these up, but this results in the results being too high as its by each order line not each overall number. I can't just flag the first line of each order as when viewed in excel the end user needs the whole order flagged.

Is there a way to create a distinct count of the order number but only when the flag is set to 1? Or is there a better method?

Thanks

Solution

  • I've resolved this issue, by creating a dimension for the order value banding and using a distinct count measure of orders split by this measure