Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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
Solved! Go to Solution.
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.
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
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.
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.
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.
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.
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.
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.
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.
User | Count |
---|---|
90 | |
88 | |
88 | |
79 | |
49 |
User | Count |
---|---|
153 | |
145 | |
106 | |
74 | |
55 |