Reply
Member
Posts: 81
Registered: ‎02-09-2017
Accepted Solution

Matrix Rows Based on Date Slicer

Hello community,

 

Is it possible to have a date slicer dynamically changing a Matrix rows?

 

For instance: if min date of slicer is 28/nov/18 (today) Matrix rows should displays all days of last week. 

 

Sample : https://drive.google.com/open?id=1CzKayMIk99I3JJ4qH7mhEILIuW3nF4Ns

 

Thanks in advance


Accepted Solutions
Community Support Team
Posts: 2,506
Registered: ‎03-15-2018

Re: Matrix Rows Based on Date Slicer

Hi @Fcoatis

To workaround this,

1.create a new table as a new calnedar table used in slicer.

enter in "New Table"
slicer table = CALENDAR("01/01/18","12/31/18")

Create calculated columns in this table
Weeknum = WEEKNUM([Date],2)
2. add 'slicer table'[date] in the slicer instead of Calendar[date].
create measures in  'slicer table'
min selected = MIN('slicer table'[Date])

weeknum_selected = WEEKNUM([min selected])

weeknum_selected-1 = WEEKNUM([min selected])-1

LastWholeWeek = IF(MAX('Calendar'[Weeknum])=WEEKNUM([min selected])-1,1,0)
3. add in Visual level filter as below
12.png
 
Finally, 
11.png
 
Please see my solution in Page5
 
Best Reagrds
Maggie

View solution in original post

Attachment

All Replies
Member
Posts: 83
Registered: ‎11-16-2018

Re: Matrix Rows Based on Date Slicer

Hi,

 

I think you can use a relative date slicer for this.

 

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

 

Member
Posts: 81
Registered: ‎02-09-2017

Re: Matrix Rows Based on Date Slicer

Thanks for the suggestion,

 

but I have a Visual that depends on current date slicer filter, I wanted the other one (Matrix) to dynamicaly change rows.

 

Best regards

Member
Posts: 83
Registered: ‎11-16-2018

Re: Matrix Rows Based on Date Slicer

Hi,

i think you have to think different. :-)

The date slicer filters on page level and should return the date range.

The more granular filter for the visual, you should set on the visual level.  (Maybe with a Top filter on Date.)

Community Support Team
Posts: 2,506
Registered: ‎03-15-2018

Re: Matrix Rows Based on Date Slicer

Hi @Fcoatis

To workaround this,

1.create a new table as a new calnedar table used in slicer.

enter in "New Table"
slicer table = CALENDAR("01/01/18","12/31/18")

Create calculated columns in this table
Weeknum = WEEKNUM([Date],2)
2. add 'slicer table'[date] in the slicer instead of Calendar[date].
create measures in  'slicer table'
min selected = MIN('slicer table'[Date])

weeknum_selected = WEEKNUM([min selected])

weeknum_selected-1 = WEEKNUM([min selected])-1

LastWholeWeek = IF(MAX('Calendar'[Weeknum])=WEEKNUM([min selected])-1,1,0)
3. add in Visual level filter as below
12.png
 
Finally, 
11.png
 
Please see my solution in Page5
 
Best Reagrds
Maggie
Attachment
Member
Posts: 81
Registered: ‎02-09-2017

Re: Matrix Rows Based on Date Slicer

Thank you Maggie,

 

That´s what I was trying to figure out.

 

Kind regards.

 

Fabio Coatis

Highlighted
Member
Posts: 81
Registered: ‎02-09-2017

Re: Matrix Rows Based on Date Slicer

Just for those interested in my adaptation of Maggie´s solution, I´m sharing the knowledge.

 

Thank you guys.

 

https://drive.google.com/open?id=1MnQ5HzmCs37oK3oXbNM13kiQOW-R2H_6