Month Year column from Date table shows all months

[Anonymous]

Hi All,

I have a date table and i am using a "Month Year" column as a common slicer for the visuals on the report. "Month Year" resides in Date table. Date table is created based on the CALENDER(MIN(table1.date,MAX(table1.date)).

Now the problem is that "Month Year" shows the entire list of months thats available in the date table e.g Jan2018-Feb2020 but in my visuals and report i am only showing data from Jun-2019(there is a visual filter). Due also sees the months for which there is no data because the visual filter is there.

What i am looking for is that this slicer should only show months based on the "Visual Filter"

i would like that

[v-lili6-msft]

hi  @Anonymous 

You could try this way as below:

Step1:

Create the relationship between date table and raw data table, and keep the cross filter direction is "Both"

Step2:

Drag Month Year field into a slicer and value from raw data table into visual filter of this slicer and set its filter is not blank.

here is sample pbix file, please try it.

and if not your case, please share your sample pbix file and your expected output.

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft ,

I have attached the a sample pbix here with the relationship i am using. 

Step1:

Create the relationship between date table and raw data table, and keep the cross filter direction is "Both"

I am only able to set up cross filter direction with one of the table but not both, i get ambiguity error when i try to create bi directional cross filter

Please take a look at the sample pbix and also the visual level filter calc "After go live" which i am using on the visuals. The one thats there is based on create date however in some visuals its has to be based on "Resolved" date as well. If i can get one working then the others should also work.

Now when i user changes the "After go live" filter the months will change to and hence the slicer should also change. dont know how to go about it .

https://drive.google.com/file/d/1BcJu80EXSwDq7GbjlZmeQJCq2yeBhHrW/view?usp=sharing  
https://drive.google.com/file/d/1BcJu80EXSwDq7GbjlZmeQJCq2yeBhHrW/view?usp=sharing 

[v-lili6-msft]

hi  @Anonymous 

I couldn't get access to this pbix file, please re-upload.

Regards,

Lin

[Greg_Deckler]

Move your visual filter to a page level filter.

[Anonymous]

Hi Greg,

Tried that, however that did not work. May be because the visual filters are coming from raw data table and the "Month Year" is coming from Date Table.

[amitchandak]

When you have date table, You should join to that and all your date related slicer should come from that. In case your date has timestamp, create a new date column

date = table[timestamp].date

[Anonymous]

Hi Amit,

This slicer i am using is coming from the date table itself , i have 2 unrelated tables for which i want to use a common slicer and its fine working too, but the problem is that the slicer gives me all the months years based on the date table and not based on the tables which has the raw data.

[amitchandak]

Make the join Bi-direction and check. Another option calendar to restricted to Min and max of table

Date = calendar(Min(Table[Date]),Max(Table[Date]))

[Greg_Deckler]

Where is your slicer coming from, your Date table? Have you tried making your relationship direction Both?