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
Anonymous
Not applicable

How to apply different filters for each visual on same report page based on slicer selection date

Hi Guys, I have one quick question on Power BI, basically i have one report which shows client details with consumed products. There are filters on date/month, and client but once i select client , i need to show last six months history of product utilized by a client in one visualization.

Is it possible to view this info in same report? because history data also available in same data set but once i filter, we can't go back to previous six month.

I can use visual level filter or even i can stop interaction for last six month data showing visual but it doesn't help because filter need to be run by user slicer selection, and all visuals need to dispalyed in same page.

 

Any suggestion?

 

Thanks,

Niel

12 REPLIES 12
V-lianl-msft
Community Support
Community Support

Hi @Anonymous ,
 
Is this problem sloved?
If not, please let me know.
 
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Sure, I'll try it out again. 

Ashish_Mathur
Super User
Super User

Hi,

You may refer to my solution here - Flex a Pivot Table to show data for x months ended a certain user defined month.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thanks Ashish but I'm trying to get this done in Power BI Report. Your solution is closet match for excel based reporting.

Hi,

My solution will work in PowerBI Desktop as well.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Ashish, I downloaded Flexi months and it worked but I'm not sure, how this can be convert into Power BI solution. My problem having multiple visuals and only one visuals should trace back 12 months based on month-year/date selection. 

 

Cam you please put this in power bi report or expalined the steps 

 

Thanks

 

Hi,

Open a PowerBI Desktop file and go to Import > Excel file.  Then looking at what i have done in the MS Excel file, drag fields from the respectie tables into yor visual.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
V-lianl-msft
Community Support
Community Support

Hi @Anonymous ,
 
You can create a calendar table and use the date column of the table as a slicer.
You also need to create a measure that displays data for the last six months based on date slicer,
 
Measure =
var slicer_value = SELECTEDVALUE(DimDate[Date])
return IF(MAX(Sales[Date]) <= MAX ( DimDate[Date] )
            && MAX(Sales[Date]) >= EDATE ( MAX(DimDate[Date]), -6 ),1,0)
 
and then apply measure=1 to the visual filter.
remove_duplicates.png
You can refer to this pbix.
 
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks Liang. Let me try this out

Anonymous
Not applicable

Hi Liang,

 

I tried your solution but it didn't work because in your sample report , you don't join date dimension with sales fact table so it work.


In my case, I have to join all other event tables with date dimension, and my reporting period slicer come from same date dimension, once i select date from slicer date dimenion table get filter for selected MonthYear so i can't open up last 12 month period to show on visusal.

 

Please refer sample screenshot of my report, there 3 visuals and highlighted top right visual should display last 12 months, based on selected MonthYear.

 

For example: User select Date from slicer: 201911, then highloghted visual should display MonthYeat starting from Dec 2018 but once slicer selected 201911 then date dimenion get filtered with 201911 so i can't display 12months on expected visual. Rest visual has to show selected month data.

Untitled.png

 

 

 

 

 

 

 

 

 

 

Thanks,

Niel

Hi @Anonymous ,
 
Again, you need to create an unrelated calendar table and use this date column as a slicer.
For detailed steps, please refer to the pbix I provided earlier.
 
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
saraMissBI
Resolver I
Resolver I

Hi @Anonymous 

I got confused about the filters and slicers you are applying and what result you would like to get. Could you please provide snapshots or sample visuals to see what you have and what you want?.... to be able to help

 

Thank you!

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.