Calculate measure using [field1] - [field2] > x as filter

Question

I have a fact table containing columns OriginalPrice and PaidPrice for customer transactions. I would like to know how many transactions befenit from a discount OriginalPrice - PaidPrice between 10 and 20 dollars. I already have a measure #Customer that describes the number of customers.

This is for a PowerBI report using a Live connection to SSAS. New columns and some DAX functions are not available in this connection mode.

DiscountTier1 = CALCULATE([#Customer],(FactTable[OriginalPrice]-FactTable[PaidPrice]) >= 10, FactTable[OriginalPrice]-FactTable[PaidPrice]) < 20)

By doing this I want to know the number of customers that had a discount between 10 and 20 dollars.

Currently I have an error as follows: CALCULATE has been used in a True/False expression that is used as a table filter expression. This is not allowed

Any suggestions of how to achieve this or what I am doing wrong? Thank you!

You can only pass basic filtering as arguments of `CALCULATE` for this sort of thing, you need to use `FILTER`. — Alexis Olson, Jan 14 '19 at 17:09

score 4 · Answer 1 · answered Jan 14 '19 at 16:26

Add the FILTER function as the second parameter of CALCULATE, and in this you can filter the fact table for records satisfying your criteria. I'd also recommend using the AND function for better readability and long term maintenance.

DiscountTier1 =
CALCULATE (
    [#Customer],
    FILTER (
        FactTable,
        AND (
            FactTable[OriginalPrice] - FactTable[PaidPrice]
                >= 10,
            FactTable[OriginalPrice] - FactTable[PaidPrice]
                <= 20
        )
    )
)

score 2 · Accepted Answer · answered Jan 14 '19 at 16:16

2

You are searching for a measure like this:

DiscountTier1 = 
COUNTROWS(
    FILTER(
        SUMMARIZE (
        'FactTable';
        'FactTable'[customer_id];
        "DISCOUNT"; 
            CALCULATE(SUM(FactTable[OriginalPrice])) - CALCULATE(SUM(FactTable[PaidPrice]))
         );
     [DISCOUNT] <= 20 && [DISCOUNT] >= 10
    ) 
)

This query calculates the discount of all rows, and filter the rows with a discount between 10 and 20

answered Jan 14 '19 at 16:16

Víctor Beltrán

593
5
12

This was super helpful, countrows gave me exactly what I was looking for. Thank you – Dranes Jan 14 '19 at 20:43

Calculate measure using [field1] - [field2] > x as filter

2 Answers2