Can you create an SSAS Tabular measure with filter constraints?

Question

I am trying to get my head around how to design the SSAS tabular model for a scenario where I have an attribute on a dimension that will modify an otherwise straight-forward measure on a related fact table.

The fact table is currently at 155 million rows. The dimension has 6200 rows. The measure we need sums a unit volume fact, then multiplies that sum by a factor that belongs to the dimension. The context used for this measure has some constraints, then: the aggregation can never include multiple rows from the related dimension. It doesn't make any business sense to do so.

I started with this, which I know is wrong: =SUM([Unit Volume])*Products[Foo Factor], and of course the designer complains because the related attribute has many values without a specific context.

My work-around for now is to create a calculated column that brings the factor into the fact table: =RELATED(Products[Foo Factor]), and then I can create a measure that looks like this: =SUM([Unit Volume])*AVERAGE([Foo Factor]).

I don't like the idea of putting calculated columns on that fact table. It's huge and it only going to get bigger. I also don't like that there doesn't appear to be a way to put constraints on the context for a given measure so I can ensure the we don't roll up volume across multiple products. Or is there?

It seems to me that ours is not an unusual scenario, but I'm too new to DAX to know how to model it correctly. If I were in my familiar SQL world, I would use a windowing function like this:

select sum([Unit Volume]) over (partition by <context goes here>) as [Unit Volume Total]

Can I create something like this in a measure with DAX?

UPDATE It seems like this should work, but the designer still barks:

=CALCULATE(SUM('My Facts'[Unit Volume])*Products[Foo Factor],FILTER(Products,Products[Product Code]="0"))

My thought was that the [Product Code]="0" filter would be replaced by the current context and since [Product Code] is defined as the key on the related table, SSAS would know my intent. Alas, it doesn't.

Stuart Stuart · Accepted Answer · 2015-01-09T16:52:42

I think I may have it. Seems like I may not be able to get away from using an AVERAGE aggregation on the foo factor, but the HASONEVALUE function does enforce the constraint I want.

This should work:

=
IF (
    HASONEVALUE ( Products[Product Code] ),
    SUM ( 'My Facts'[Unit Volume] ) * AVERAGE ( Products[Foo Factor] ),
    SUM ( [Unit Volume] )
)

Can you create an SSAS Tabular measure with filter constraints?

2 Answers