ELIMINATE A SLICER TO MAKE A CALCULATION

[Isidro]

Hello,

I work with a table of several years. On the Power BI page I have two slicers, one for the season and one for the months.
Depending on the choice of slicers, I have created a formula for the amount to calculate.
In the data table there are more than 10 seasons and the slicer only appears the last 4 seasons, that is, I made a filter prior to that Slicer. When I want to make a calculation when there is no season, I do not get what I want because for Power BI that filter is still filtered.

Is there any formula that allows when there is no season marked to understand Power BI that there is no filter on the seasons?

Thank you very much and best regards.

[TomMartens]

Hey,

without sample it's difficult to answer your question, so this is a general explanation:

Basically each measure aggregates the "visible" rows of the table, this is the reason why you just cant simply write this:

Measure = 'tablename'[numericcolumn]

For this reason you have to write this:

Measure = SUM('tablename'[numericcolumn])

The rows of table are filtered down by the slicer selection and also what is on rows (table) and columns (matrix visualization)
If you want to consider slicers (reduced rows) in your measure, you have to tell your measure to consider slicers, this is done by wrapping your measure with the CALCULATE(...) function like so:

Measure = 
CALCULATE(
  SUM('tablename'[numericcolumn])
)

CALCULATE has at least one parameter, the first parameter is the expression like SUM(...), now all slicers, row and/or column values are filtering the tablerows, if you now want that "expand" the rows again, by not considering a column (e.g. a column that is used as slicer), you provide a 2nd, a 3rd, a ... parameter to the CALCULATE(...) function like so:

Measure = 
CALCULATE(
  SUM('tablename'[numericcolumn])
  ,ALL('tablename'[columninslicerthatshouldnotbeconsidered])
)

This is called the "FILTER CONTEXT".

Hope this keeps you going

[Isidro]

Hi @TomMartens,

I have created the following formula:

N-1 = SWITCH(
    TRUE();
      AND(ISFILTERED(CalendarioContable[Temporada]);
       ISFILTERED(CalendarioContable[Mes]));
        CALCULATE([Saldo N];
                                            DATEADD(CalendarioContable[Fecha];-1;MONTH));
      ISFILTERED(CalendarioContable[Temporada]);
        CALCULATE([Saldo N];
           SAMEPERIODLASTYEAR(CalendarioContable[Fecha]));
      NOT(AND(ISFILTERED(CalendarioContable[Temporada]);
       ISFILTERED(CalendarioContable[Mes])));0)

The problem is that the slicer created with CalendarioContable [Temporada] is filtered so that it only shows the last seasons. Therefore, whenever ask ISFILTERED (CalendarioContable[Temporada]), the answer is TRUE, although it has not marked any season in the slicer.

Thank you very much and best regards.

[TomMartens]

Hey,

without any sample data, it's quite cumbersome and maybe just guesswork what's going on ... Please prepare sampledata upload the PBIX to OneDrive or Dropbox and share the link.

Cheers