filter function charts

[vitexo87]

I have a bar graph, I need it to work as follows: By selecting any year in the filter values to be displayed in the chart must be the values of all previous years and it takes place a Texo box or similar Meeting that presents the total value of values by the year selected in the filter.

it is possible to do this in Power BI? if it is not possible to make a bar graph with what type of chart is possible I do this?

is an example of how I want to leave my graph:

[v-shex-msft]

Hi vitexo87,

According to your description, you want to get the records filtered by date is equal or less than your choice in slicer, right?

Below is my test steps:

 1. Create test tables.

'Sales Amount Records':

 

‘CalendarTable’:

Dax: CalendarTable = ADDCOLUMNS( CALENDAR(MIN('Sales Amount Records'[Date]),MAX('Sales Amount Records'[Date])),"Year",YEAR([Date]),"Month",MONTH([Date]))

 

Relationship(Date):

 2. Add measures to table.

‘Sales Amount Records’:

Não Processado = sum('Sales Amount Records'[Nao])

Processado = sum('Sales Amount Records'[Processado ])

‘CalendarTable’(Use to get the value of slicer):

Slicer Choice = if(HASONEVALUE(CalendarTable[Year]),MAX(CalendarTable[Year]),BLANK())

 3. Create a table get data from slicer.

Dax: CALCULATETABLEBYSlicer = CALCULATETABLE('Sales Amount Records',FILTER('Sales Amount Records',YEAR('Sales Amount Records'[Date]) <=[Slicer Choice]))

 4. Create visuals.

Slicer Year:

 

Slicer Month:

 

Current choice records:

 

Requirement table:

 

Test result:

In summary, I found that the data which choice in slicer can’t alternately with the created table. When the dax formula initialization the table, the measure(‘Slicer Choice’) is blank. (it can’t get the value which selected in the slicer). The table’s structure will be fixed after initialization.

To work around the issue, you can use below two methods:

 a. Use the page level filter with “is on or before” operator

 

 b. Use Timeline slicer.

Regards,

Xiaoxin Sheng

[vitexo87]

@v-shex-msft

I already had a calendar table, however I can not relate to any other table because the ano_id that would be the key doubles

I was trying to create a column in my Year table every time it was the year of filter values to be displayed by this new column was every year until the filtrate year.

i try create conditional columns, measurement, custom column but without success

[v-shex-msft]

Hi vitexo87,

>>By selecting any year in the filter values to be displayed in the chart must be the values of all previous years and it takes place a Texo box or similar Meeting that presents the total value of values by the year selected in the filter.

The slicer not support within feature, you can use 'page level filter' or 'timeline slicer' to achieve your requirement.

For detail info, you could refer to my previous reply.

Regards,

Xiaoxin Sheng

[vitexo87]

the expression that is being used in this graph

Não Processado = sum(Fatos[Vl_RestoNaoProcessado])

Processado = sum(Fatos[Vl_RestoProcessado])