Filter based on minimum of a subset of data

[bkr]

Hi folks,

I'm struggling with a summarytable in which I'd like to filter by year first and then by by a minimum of another table.

Say this is the starting Table1 (much simplified, of course):

Year	Sales	Topic
2018	5	1
2018	10	3
2019	6	2
2019	8	3
2019	11	4

Now I want to create a Summarytable:
Summarytable =
SUMMARIZE('Table1',

  "FilteredSales"; CALCULATE(
    SUM('Table1'[Sales]),
    FILTER('Table1'; 'Table1'[Year] = 2019),
    FILTER('Table1'; 'Table1'[Topic] = MIN('Table1'[Topic]))
  )

)

Of course, this returns a table with a single cell but that's for demonstration purpose only.
I would have expected this filters by year first, leaving the values for 2019 only and then give me the sales for the entry with the smallest number in Topic, hence return "6".

However, it returns nothing because there is no entry for 2019 that also has the smallest number in Topic.

How can I achieve a nested filter that filters by one column first and then applies the MIN function only on the result of that first filter?

N.B.: "Year" could also be any other column, so date functions won't help.

[JarroVGIT · Accepted solution]

Try this:

Summarytable = 
VAR tmpTable = FILTER('Table1', 'Table1'[Year] = 2019)
RETURN
SUMMARIZE(tmpTable,
  "FilteredSales", 
    SUMX(FILTER(tmpTable, [Topic] = MINX(tmpTable, [Topic])), [Sales])   
)

Tested it myself 🙂 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[JarroVGIT · Accepted solution]

Try this:

Summarytable = 
VAR tmpTable = FILTER('Table1', 'Table1'[Year] = 2019)
RETURN
SUMMARIZE(tmpTable,
  "FilteredSales", 
    SUMX(FILTER(tmpTable, [Topic] = MINX(tmpTable, [Topic])), [Sales])   
)

Tested it myself 🙂 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[bkr]

Hi Djerro,

thanks, this in principle works. However, it seems as i narrowed down the problem too much. In another column of my summarytable I need to do some work with the 2018 data which is not possible because all the 2018 data is lost in the tmptable. Is there a way to nest the filters only for a specific column?

[JarroVGIT]

Please provide your expected end result as a table and I will adjust the solution accordingly. Thanks!

[bkr · Accepted solution]

I found it myself based on your suggestion:

The trick is to do a

NewTable = 
SUMMARIZE('Table1',
"FilteredSales", CALCULATE(
SUM('Table1'[Sales]),
FILTER('Table1'[Topic] = MINX(FILTER('Table1'; [Year] = 2019), [Topic])))

This might decrease performance but that way I still can further work with the original table to create other columns