display data in a certain range with a single select slicer

[Anonymous]

Hello, experts, 

I want to built a line chart from a dimension table with year-month column, and a fact table, the two table is connected with yearmonth column, and the slicer is single-select which from year-month column, the data type is string and should not convert into date type because some other reasons.

here is the data looks like, 

I want to realize that create a line chart visual with the x-axis from dimension table because there are other measures need to display from other fact table;

when the slicer select 202206, then the line chart visual x-axis and relevent data should be start from 202201 and end at 202206, if slicer is 202105, then the x-axis and relevant data should be start from  202101 and end at 202105.

this problem is quite complicate for me, do you have any good ideas, Thank you.

[amitchandak]

@Anonymous , when you select 202206  - 202201 to  202206

then why 202101 to 202205 for 202105?

[Anonymous]

it's a mistake, it should be 202105

[amitchandak]

@Anonymous , Create a new table

Month = distinct(Table[Year Month])

Do not join it with your table only use in the slicer

use below measure with month year from your table

Sum of Value  =

var _max = Maxx(allselecetd(Month), Month[Yearmonth])

var _min = left(_max&"",4)*100 + 1 

return

calculate(sum(table[Value]), filter(Table, Table[Yearmonth] >= _min && Table[Yearmonth] <=_max))

[Anonymous]

Thanks for your answer, actually I have to join the table because there is another table in the same page that use the same axis and same slicer

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

For your case, we implemented your needs with "Filter on this visual", but you said that there are other visuals on your report that need to be filtered by your filter. According to research, if your slicer filter all meet your above needs:

For your case, we implemented your needs with "Filter on this visual", but you said that there are other visuals on your report that need to be filtered by your filter. According to research, if your slicer filter all meet your above needs:

when the slicer select 202206, then the line chart visual x-axis and relevent data should be start from 202201 and end at 202206, if slicer is 202105, then the x-axis and relevant data should be start from  202101 and end at 202105.

You can try the following to keep the 'Table' [datamonth] slicer separate instead of using two slices. If your filtering logic is different, then you need to keep two slicers on the top of your page.

Here are the steps you can refer to :
(1)We need to download the "Preselected slicer" in custom visual :

(2)Then we need to create a 'Flag' table, like this:

(3)Then we create a measure :

Measure 3 = var _slicer = SELECTEDVALUE('Slicer'[datamonth])
var _first =VALUE(LEFT( FORMAT(_slicer , "0"),4)&"01")
var _table_datamonth = SELECTEDVALUE('Table'[datamonth])
return
IF( _table_datamonth >= _first && _table_datamonth<=_slicer ,TRUE() ,FALSE())

(4)Then we put these fileds on this visual :

Then we can configure the visual's color to white to ignore the visual on our report and just use the 'Table'[datamonth] slicer:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly