Summarize with filtered measure

[nainiu440]

Hello,

I am watching the tutorial about using the function summarize and the example is showed below:

I am wondering is that possible to put a filter on the measure of total sales? for example, only total sales larger than 1,000,000 will be summarized in this table. I tried this:
summarize =
VAR NoFilter = SUMMARIZE(‘Regions Table’, ‘Regions Table’[City], “Total Sales”, [Total Sales])
RETURN
FILTER(NoFilter, [Total Sales] >1,000,000)

but, it showed an error said “invalid bindings specified”. What is the correct way to achieve this? thanks!

[BrianJ]

@nainu,

Try this:

Virtual Table Filtered = 
FILTER(
    SUMMARIZE(
        Regions, Regions[City],
        "Total City Sales", [Total Sales]
    ),
    [Total City Sales] > 1000000
)

I’ve not seen that “invalid bindings” error before , but “1,000,000” (as opposed to 1000000) in your filter condition will cause problems.

• Brian

[Nick_M]

@nainiu440,
Definitely never seen that error before, but looks likes @BrianJ solution works.

Also, might want to be careful adding calculated columns in the summarized function. Usually not a problem but when you mix together a filter and the row context in one function it can lead to some interesting results and could be a potential performance hit. I’d use AddColumns and then summarized

ADDCOLUMNS(
   SUMMARIZE(
       ‘Regions Table’, 
       ‘Regions Table’[City]
    ),
    “Total Sales”, [Total Sales]
)

[BrianJ]

@Nick_M,

Thanks - nice catch.

[nainiu440]

thanks all, that works!