Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MiKeZZa
Post Patron
Post Patron

Slicer with 2 options; show only last month or everything

Hi all,

 

I'm a little lost; I want to present my users a slicer where they'll 2 options: show everything or only show last month. But what I've been trying now is resulting in calculated columns based on my datedim. Then they are like this:

 

Show everything or last month = if(date[Is last month]="Yes";"Show only last month";"Show everything")

 

But what happens is complete right but not what I want: if people choose "Show everything" you see everything except the last month. I think the solution will be easy, but for now I don't see the right solution.... Anyone who can help?

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @MiKeZZa 

One way or another, you will need another table to to allow selection of different periods that overlap.

 

Possible methods:

  1. You could follow an approach similar to this post from Chris Webb.
    • In this post a Period table is created that maps multiple "Periods" to sets of Dates.
    • The Date column of the Period table then has a 1:many bidirectional relationship with the 'Date'[Date] column.
  2. Since you only have two Periods filters of interest, you could use a similar but possibly simpler approach making use of the Is Last Month column you have already defined, and a many:many relationship with a Period table. See sample PBIX here.
    • The logic is similar to Chris Webb's post, but the Period table only needs to map Periods to values of the Is Last Month column, so it can look like this:image.png
    • Then create a many:many relationship between Period[Is Last Month] and 'Date'[Is Last Month] with filter direction set so that Period filters 'Date'.
    • If you wanted to avoid many:many relationships, you could create an Is Last Month table related to 'Date', then have a 1:many relationship between Is Last Month and Period.

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

Hi @MiKeZZa 

One way or another, you will need another table to to allow selection of different periods that overlap.

 

Possible methods:

  1. You could follow an approach similar to this post from Chris Webb.
    • In this post a Period table is created that maps multiple "Periods" to sets of Dates.
    • The Date column of the Period table then has a 1:many bidirectional relationship with the 'Date'[Date] column.
  2. Since you only have two Periods filters of interest, you could use a similar but possibly simpler approach making use of the Is Last Month column you have already defined, and a many:many relationship with a Period table. See sample PBIX here.
    • The logic is similar to Chris Webb's post, but the Period table only needs to map Periods to values of the Is Last Month column, so it can look like this:image.png
    • Then create a many:many relationship between Period[Is Last Month] and 'Date'[Is Last Month] with filter direction set so that Period filters 'Date'.
    • If you wanted to avoid many:many relationships, you could create an Is Last Month table related to 'Date', then have a 1:many relationship between Is Last Month and Period.

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you. So easy, so simple. But I had a lockdown of my brain 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.