cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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)

1 ACCEPTED SOLUTION
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.

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.

9 REPLIES 9
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

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.

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.

Post Patron

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?

Community Support

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.

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.

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.

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.

Go to My LinkedIn Page

Post Patron

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.

Super User

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.

Go to My LinkedIn Page

Post Patron

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.

Super User

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.

Go to My LinkedIn Page

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors