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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Razorbx13
Post Patron
Post Patron

Passing a Filter into multiple pages of a Report

Hi all, how can it pass a filter selected by a user into three matrixes as noted below with these conditions.  Right now I have individual filters on each page but they each perform something different:  On or before, Equals, selection based on the month end.

 

User selects Month End = 1/31/2021

 

Matrix 1 - Month End on or before 1/31/2021

Matrix 2 - Month End = 1/31/2021

Matrix 3 - Current Fiscal year through 1/31/2021 (basically as columns they see all months from 7/31/2020 to the filter date of 1/31)

 

Thanks in advance

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @Razorbx13 ,

 

You don't have to create 15 measures. You only need to create 3 measures for different matrices.

The fiscal year is assumed to start in August.

Matrix 1 = IF(MAX('Sales'[Date])<=SELECTEDVALUE('Table'[Month End]),1)
Matrix 2 = IF(SELECTEDVALUE('Table'[Month End])=MAX('Sales'[Date]),1)
Matrix 3 = IF(CALCULATE(MAX('Sales'[FY]),FILTER(ALL('Sales'),[Date]=SELECTEDVALUE('Table'[Month End])))=MAX('Sales'[FY])&&MAX('Sales'[Date])<=SELECTEDVALUE('Table'[Month End]),1)

Then put these three metrics into the visual level filter of the matrix, and set Show items when the value is 1.

10.png11.pngScreenshot 2021-04-27 134649.png

 

Screenshot 2021-04-27 134433.png

 

 

 

 

Best Regards,

Stephen Tao

 

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

View solution in original post

9 REPLIES 9
v-stephen-msft
Community Support
Community Support

Hi @Razorbx13 ,


Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.


Best Regards,
Stephen Tao

v-stephen-msft
Community Support
Community Support

Hi @Razorbx13 ,

 

You don't have to create 15 measures. You only need to create 3 measures for different matrices.

The fiscal year is assumed to start in August.

Matrix 1 = IF(MAX('Sales'[Date])<=SELECTEDVALUE('Table'[Month End]),1)
Matrix 2 = IF(SELECTEDVALUE('Table'[Month End])=MAX('Sales'[Date]),1)
Matrix 3 = IF(CALCULATE(MAX('Sales'[FY]),FILTER(ALL('Sales'),[Date]=SELECTEDVALUE('Table'[Month End])))=MAX('Sales'[FY])&&MAX('Sales'[Date])<=SELECTEDVALUE('Table'[Month End]),1)

Then put these three metrics into the visual level filter of the matrix, and set Show items when the value is 1.

10.png11.pngScreenshot 2021-04-27 134649.png

 

Screenshot 2021-04-27 134433.png

 

 

 

 

Best Regards,

Stephen Tao

 

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

Cool.  Think I am beginning to understand.  So this is actually three matrices, each one off a different table but all being filtered using a month end field from a dated table joined to each fact table.  So does this need to be modified due to that?  Do I add it into each table?

Hi @Razorbx13 ,

 

The key point is that the date column in your slicer cannot be related to the date column in your matrix. Therefore you do not need to make changes. Just put my measures into your model.

 

 

Best Regards,

Stephen Tao

 

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

 

Jihwan_Kim
Super User
Super User

Hi, @Razorbx13 

Please correct me if I wrongly understood your question.

In my opinion, it is better to have a separate date-table for the Slicer-Purpose.

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file, and all steps are numbered in front of each measure.

 

Picture3.png

 

 

https://www.dropbox.com/s/7twms0kdyxc186n/razorbx.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Thank you!  I will check this out.  I have a date table.  I forgot to mention the three matrixes come from three different tables.  Those three tables are joined to the single date table.  So basically the month end they choose, whether this month or three years ago always does the same three things.  Just depends on the month end they choose as to what dates overall passed.

 

One more question, your question.  If my matrix has like 15 measures, do I need to place the dax in each one?  Example below are my column headers in one of them.

 

2021-04-24_7-52-52.jpg

Hi, @Razorbx13 

Thank you for your feedback.

Please correct me if I wrongly understood.

Even you have three fact tables, I think the logic is similar. You want to have one slicer, but make one action influence differently to each fact-table's visualization, am I correct? 

If I am not wrong, in this case, it is needed to have a separate slicer table, and each visualization's DAX measures have to be written in a way to correspond differently to that selection.

I hope I explained well..

Thank you.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

No you have me understood correctly.  Three fact tables all joined to the same dates table.  And if I understand you correctly for each of those fact tables I will need to modify my current measures, each one, to perform the same Dax filtering.  So if one fact travel has already 15 measures that will be 15 measures I have to account for.  Correct?  Although tedious that should be easy. 

I cannot say 100% convincingly because I did not see your model, but I want to say that can be one way.

Maybe, if I can see your sample pbix file, another way can be suggested.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.