Forum Discussion

fabiocesarei's avatar
fabiocesarei
Advocate I
10 years ago

2 filter on summarize table

Hi , I want to create a table by summerize function, but I need to insert two filter conditions (I need to filter the data by year and by an " X " field of the data set), is it possible?
Need Help
AlexChen's avatar
AlexChen
Microsoft Employee
10 years ago

Hi,

 

I advise you to use “SummarizeColumns” function to do it.

 

Now I assume you have a table called “prod” like below.

 

 

You can create a table use the following code.

The products that are created before 2010 and amount value that are less that 2000 are filtered.

 

Table = SUMMARIZECOLUMNS(

                                      prod[product],

                                      filter(prod, year(prod[createdDate]) > 2010 && prod[amount] >= 2000),

                                      "amt", sum(prod[amount])

                                      )

 

 

Best Regards

Alex

 

 

  • fabiocesarei's avatar
    fabiocesarei
    Advocate I
    10 years ago

    Hi Alex, thank for reply. Filter is supported in summarize, I have used this example SUMMARIZE(FILTER('Time';'Time'[year]=2015);'Time'[quarter]; "CostQtr";SUM('Fact_costs'[.......
    and it worked.
    perhaps power bi converts the function.
    I have to use 2 fields of different tables in filter function, do I have to create a previous table with a filter option and the second field as column and use it as filter in the final one?

    Thanks

    Fabio