How to use MeasureExpression In SSDT?

Question

I have sth like this in my fact table :

a dimenstion named dimType that can have these values "FIND , LOSE , GIFT" and a measure named Count that has a positive number

I want this measure in SSDT by SUM AggregateFunction but with a little change, before aggrigation I want to negetive this Count if it's of type LOSE ex :

Type : FIND    / Count :12
Type : LOSE  / Count : 5
Type : GIFT   / Count :  4

THEN : SUM(Count) = 12-5+4 = 11

Should I use MeasureExpression in Measure Properties ? How can I do that?

Answer 1

Actually, the easiest way to do it is SQL:

iif(type = 'LOSE',-count,count)

If you need it for some reason both positive and negative aggregation you can you use MDX calculations.

Simple one:

[Measures].[Count] - ([dimType].[Type].[LOSE],[Measures].[Count]) * 2

Universal one:

Sum(
    Existing [dimType].[Type].[Type].Members,
    Iif(
        [dimType].[Type].CurrentMember is [dimType].[Type].[LOSE],
        -[Measures].[Count],
        [Measures].[Count]
    )
)