CALCULATE – More than 1 filter criteria on the same column

[porter444Participant Started: 10Replies: 24Total: 34]

Is there an easier way?

Return Order Count:=CALCULATE([Order Count],FILTER(‘counter sales data’,’counter sales data'[Order Type]=”CO”))+CALCULATE([Order Count],FILTER(‘counter sales data’,’counter sales data'[Order Type]=”CR”))

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Yes, there are at least three ways to accomplish your objective:

1. Use the OR() function

Return Order Count :=
CALCULATE (
    [Order Count],
    FILTER (
        'counter sales data',
        OR (
            'counter sales data'[Order Type] = "CO",
            'counter sales data'[Order Type] = "CR"
        )
    )
)

2. Use the “logical or” operator (||)

Return Order Count :=
CALCULATE (
    [Order Count],
    FILTER (
        'counter sales data',
        'counter sales data'[Order Type] = "CO"
            || 'counter sales data'[Order Type] = "CR"
    )
)

3. Use portable formulas (a Rob Collie term). This solution consist of three measures and, if the [CO Count] and/or the [CR Count] could be used in multiple measures, is the preferred solution.

Return Order Count:= [CO Count] + [CR Count]

CO Count:= CALCULATE([Order Count],FILTER(‘counter sales data’,’counter sales data'[Order Type]=”CO”))

CR Count := CALCULATE([Order Count],FILTER(‘counter sales data’,’counter sales data'[Order Type]=”CR”))

Tom

[Author]

Posts