Dynamic Filter with selection button or slider or calendar

[Anonymous]

Hi,

I have a Matrix where i need to implement a filter:

The user should be able to choose a year: 2013, 2014, 2015, 2016, 2017, 2018

The filter selects on the field QUARTER the 4th quarter of the selected year (2013 Q4, 2014 Q4, 2015 Q4, 2016 Q4, 2017 Q4)  except for the running year where the running quarter is chosen (e.g. 2018 Q3 on 08/7/2018) 

1. How can the user be given the posibility to choose the year?

2. How can the filter be implemented on the matrix?

Thanks

R.W.

[v-shex-msft]

Hi @Anonymous,

#1, Nope, slicer only works with select items, if you chosen quarter 4 of six years, it means your visual will only calculate with quarter 4 values of these years.

#2, Matrix visual not contain filter option, please use other filters to achieve filter effect.(different level filters, slicer)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Xiaoxin Sheng

thanks

yes that's the idea, I just need the results at the end of every year, as the results are accumulated per quarter, that would be the results at the 4th quarter of every year except of the running year as the year is not at it's end.

Can a filter be calculated on the basis of radiobuttons? Meaning:  none or just one selected of the many at any time

2013	¡
2014	¡
2015	¡
2016	¤
2017	¡
2018	¡

[Anonymous]

I found a way to make the radio button list

Edit Query - Copy the date column and rename it as Year

Edit Query - Transform - Year

Add a Slicer in List form

Add the newly created Year column

Filter on the date column select the dates of the 4th quarters of every year and the date in the current quarter

Under Selection controls - Single select on

One important problem stays: I can't make the filter dynamic. Every day I need to change the filter.

R.W.

[v-shex-msft]

Hi @Anonymous,

I'd like to suggest you add calculated columns original table to extract year and quarters, then use new column to create slicers.
One of slicer use year as source, other one stored quarter.

You can also try to use relative date slicer mode to achieve dynamic slicer effect.(for normal slicer with list mode, it is impossible to achieve dynamic filter)

Use a relative date slicer and filter in Power BI Desktop

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks Xiaoxin Sheng,

that is what I did (see my previous answer)

But I think I need a calculated column SELECT that calculates =OR(MONTH(DATE) = 12; DATE = TODAY() - 1)

In this way its easy to add the filter:  SELECT  = True

Do you know how to calculate this expression in a calculated column?

R.W.

[v-shex-msft · Accepted solution]

Hi @Anonymous,

Unfortunately, power bi not support to create dynamic calculated column/table based on slicer/filter.

If you mean let formula only calculate with specific date range, it is possible.

Sample:

Column formula =
IF (
    OR ( MONTH ( Table[DATE] ) = 12; Table[DATE] = TODAY () - 1 );
    'measure formula';
    BLANK ()
)

Use your calculation formula to replace bold part, it only calculate on December and yesterday.

Regards,

Xiaoxin Sheng