How to apply filter to Power BI table

Some times when you work with data you need to apply filter by product, manager, other aggregation conditions:

Table 1

Product Name	Amount	Manager
Product A	1000	Tim
Product B	2000	Tim
Product A	500	John
Product C	300	Maya

Applied filter in Excel pivot table:

To create aggregation table like table 1 with no filters we could use dax expression:

Sales_PivotTable = SUMMARIZE(Sales_Table,Sales_Table[Product Name],"Total Sales",SUM(Sales_Table[Amount]))

In Power BI when we create table after then we could not apply filter on model level. That is why you need set filter before creation the table if you need then do some addition calculations. Approach to apply filter will based on if the condition is constant or not:

1. for constant condition you could apply filter in the formula:

General syntax of the function that we use: FILTER(table, filter) . This function we could paste direct using function SUMMARIZE in argument field where it is required to specify table. In our case we specify table with filter:

Axample when we apply filter by manager's name Tim: Sales_PivotTable = SUMMARIZE(FILTER(Sales_Table,Sales_Table[Manager]="Tim"),Sales_Table[Product Name],"Total Sales",SUM(Sales_Table[Amount]))

1. for dynamic parameter we could create addition table Parameters that could be formed by uploading data from disk file, internet, or other sources:

In our example we have column [Params] in table where we specify parameter Name, then a column [Default_value] where we specify value of the parameter. To return value of the filter we use the formula: SELECTCOLUMNS(FILTER(Parameters,Parameters[Params]="Manager"),"Manager", Parameters[Default_value])

General syntax of the function SELECTCOLUMNS: SELECTCOLUMNS(Table, [Name], Expression, Name], …)

The filter we applied to Parameters table is the name of our parameter "Manager": FILTER(Parameters,Parameters[Params]="Manager")

We use filter in the first part of the formula where is should be table: SELECTCOLUMNS(Table,... In the second part SELECTCOLUMNS(Table, [Name], Expression, Name], …) we specify our custom column name (in our case it is "Manager") and expression that should be applied to this column (in the example it is specified the source column we use Parameters[Default_value]).

Thus we create a formula to return value of the parameter. Also we could store this value using key world "var", create variable filterBymanager:

var filterBymanager = SELECTCOLUMNS(FILTER(Parameters,Parameters[Params]="Manager"),"Manager", Parameters[Default_value]) (2.1)

In our case this expression returns all rows which contain value of the parameter "Manager"

If we know that value of the parameter always return one row, we could use such expression where direct add condition to filter expression Sales_Table[Manager]=filterBymanager:

SUMMARIZE(FILTER(Sales_Table,**Sales_Table[Manager]=filterBymanager**),Sales_Table[Product Name],"Total Sales",SUM(Sales_Table[Amount])) (2.2)

Sales_Table[Manager]=filterBymanager is mean that we select all rows from Sales_Table where column Manager equals stored variable filterBymanager

Note, that expression (2.2) will work only if the variable filterBymanager will contain one row. If you need to apply filter where there are two managers, for example Tim and Maya we should to change form of the if condition:

Sales_Table[Manager] IN filterBymanager

This condition will check if list filterBymanager variable contains the value from column Manager. Final expression is:

Sales_PivotTable = var filterBymanager = SELECTCOLUMNS(FILTER(Parameters,Parameters[Params]="Manager"),"Manager", Parameters[Default_value]) **return** SUMMARIZE(FILTER(Sales_Table,Sales_Table[Manager] IN filterBymanager),Sales_Table[Product Name],"Total Sales",SUM(Sales_Table[Amount]))

Note, when we specify variables then we must use key world "return" to evaluate and obtain resul from expression which contains variables.

Compare to aproach 1, final result is not changed but now it is could be based on dynamic data:

If we add value Maya to Parameters table we obtain also sales of her products:

We get additional amount 300. It is sales from Maya (see Table 1):

Our filter is working well.