TOPN and filters issue

[Anonymous]

Hi,

I am getting an error when doing the following:

I am getting the top 20 summarized relationtips between to columns:

So first I created a combinated column named Im-Ex on Power Query:

= Table.AddColumn(#"Renamed Columns", "Im-Ex", each [Importador] & "-" & [Exportador])

The table is totally denormalized, so everything I need to get the calculation is on the tabel (named Base) . Data is per transaction, what I need is sum of the transactions, then the top 20.

So I wrote the following DAX formula:

Top20 = 
var tempo=
  TOPN(
    20,
    SUMMARIZECOLUMNS('Base'[Im-Ex], "Sumcantidad", CALCULATE(SUM('Base'[cantidad]))),
    [Sumcantidad],
    0
  )
return
 SUMMARIZECOLUMNS(
    tempo,
    "Sumcantidad", IGNORE(CALCULATE(SUM('Base'[cantidad])))
  )

I got the correct result if I put the variable in a visual that can display single values like a Card, KPI, etc.

Now the problem...

My applications needs to display the results of the variable and has 4 slicers (all slicers come from the same table). If I use one slicer I got correct answers, no matter what filter I use. But if I use 2 slicers, I got the following error...

Error Message:
MdxScript(Model) (34, 2) Calculation error in measure 'Base'[Top20]: SummarizeColumns() and AddMissingItems() may not be used in this context.

No matter where I put the filters, could be a slicer or directly on the visual, when I get 2 filters in actions I got the error message.

Thanks in advance for the help.

Regards

[Anonymous]

I upgraded the formula to the following.

Top20test2 = 
  VAR __DS0FilterTable = 
    TREATAS(VALUES(Base[mercado]),Base[mercado])

  VAR __DS0FilterTable2 = 
    TREATAS(VALUES(Base[Año]), 'Base'[Año])

  VAR __DS0FilterTable3 = 
    TREATAS(VALUES(Base[Buckets]), 'Base'[Buckets])

  VAR __DS0FilterTable4 = 
    TREATAS(VALUES(Base[clasificacion]), 'Base'[clasificacion])

  VAR __ApplyFilterSQDS0 = 
    TOPN(
      20,
      SUMMARIZECOLUMNS(
        Base[Im-Ex],
        __DS0FilterTable,
        __DS0FilterTable2,
        __DS0FilterTable3,
        __DS0FilterTable4,
        "Sumcantidad", CALCULATE(SUM(Base[cantidad]))
      ),
      [Sumcantidad],
      0
    )

Return
  SUMMARIZECOLUMNS(
    __DS0FilterTable,
    __DS0FilterTable2,
    __DS0FilterTable3,
    __DS0FilterTable4,
    __ApplyFilterSQDS0,
    "Sumcantidad", CALCULATE(SUM('Base'[cantidad]))
  )

This time I got the base of the formula from the Perfomance Analizer, I obtenied copying the query from a card visual that displays the sum of cantidad an put the filter to Top 20 based on cantidad. The visual works fine at one, two, three or four filters applied to it.

I create the formula and get the same results, If I apply one filter it works fine, when applying the second filter the error appears again.

Regards,