Forum Discussion
Power BI/DAX: dynamically filter table values with slicer
Hi, testingname
You can try the following methods. Create a new slicer table.
Slicer = VALUES('Table'[Monthly Report])Measure = Var _rank= CALCULATE(MAX('Table'[Monthly Rank]),FILTER(ALL('Table'),[Monthly Report]=SELECTEDVALUE(Slicer[Monthly Report]))) RETURN IF(SELECTEDVALUE('Table'[Monthly Rank])<=_rank&&SELECTEDVALUE('Table'[Monthly Rank])>=_rank-5,1,0)Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi testingname
If the calculation is working has you describe the only question left to do is to take out the filter context from the calculation.
You need to have a variable that gets the max value and then introduce a REMOVEFILTERS on the syntax somethign similar to this:
Your measure =
VAR MaximumMonth = MAX(Table[Monthly_Rank])
Return
CALCULATE([Your calculation],Table[Monthly_Rank] <= MaximumMonth && Table[Monthly_Rank]>= Table[Monthly_Rank] - 5 , REMOVEFILTERS(Table[Monthly_Rank]))
This should work as expected.
In the CALCULATE portion of your DAX statement, I don't under stand what you mean by "[Your cacluation]".
Are you reffering to the column already in the data that determines if the a week is within the last 6 weeks? That's done in SQL so it's not necessarily useful for what I'm trying to accomplish. I merely displayed it for context. What you see in the first image is simply achieved by applying that SQL column as a filter on the visual. No slicer is currently being used.
I need to create a slicer that responds to a selected "Monthly Report" slicer value by filtering to the value only along with the other 5 most recent weeks.
Hi testingname ,
I refer to the Calculation you have already performed, or are you just adding the columns directly without any measure?
"Monthly Report" is currently being kept to the most recent 6 months from the max date by a column generate in SQL. Using that will not work, because the column delivers a set value based off of the MAX "Monthly Report" date.
I'm looking to create something similar, but dynamic...based off of a slicer's selected value.Hi testingname
Apologies for the question but maybe I'm not understanding how you have the calculations setup.
Is it possible to send a mockup of the pbi file?