Forum Discussion
Power BI/DAX: dynamically filter table values with slicer
I have a Power BI table visual with a column "Monthly Report" containing text values such as "2024 08 August" "2024 09 September", etc... There is an associated column "Monthly Rank" with numerical values associated with each distinct value from "Monthly Report"(In the data, any row with a "Monthly Report" column value of "2024 09 September" has a "Monthly Rank" column value of 12. Data rows with "2024 05 May" values in the "Monthly Report" column have "Monthly Rank" values of 8).
In the table visual, I'm able to show only the most recent 6 "Monthly Report" values by applying a filter: a column in the data that returns a "Last 6 Months" value...(finds max report date, returns "Monthly Rank" value from that, then Monthly Rank must be equal or no less than 5 less.) All other Monthly Rank values return NULL and NULL is unchecked in the visual filter.
The above is successful in that if the most recent Monthly Report value is "2024 09 September", only that month and the previous 5 month's reports are present in the table.
I would like improve this visual by using a Power BI slicer to select a particular "Monthly Report" value, which would then alter which "Monthly Report" values are shown in the table by showing the selected "Monthly Report" value last, with the 5 prior values preceding.
For instance, if the slicer is filtered to "2024 07 July", only 6 values would be present in the "Monthly Report" column of the table...top to bottom as "2024 02 February", "2024 03 March", "2024 04 April", "2024 05 May", "2024 06 June", and "2024 07 July".
If the slicer value is changed to "2024 10 October" then the 6 values in the table would be: "2024 05 May", "2024 06 June", "2024 07 July", "2024 08 August", "2024 09 September", "2024 10 October".
I've tried various created measures and columns with DAX to achieve the above but can't find any info on either returning an array of the 6 Monthly Rank values or being able to assign a created column value of "Filtered 6" that would populate based off of the SELECTEDVALUE of the filter.
Oddly enough, this is relatively easy to replicate in Excel, using "List Data Validation" and some simple XLOOKUP formulas.
The dropdown filter in I1 produces the 6 values in L2:L7.
I'm racking my brain for a way to reproduce this Excel logic in SQL but can't figure it out.
Any/all help appreciated.
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.
7 Replies
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.
Thank you for your response Anonymous.
This appears to be successful!
I'm using the new "Monthly Report" column from the new "Slicer" table as the Slicer Field column.
The original "Monthly Report" column from the original table is still used in the table column.
The new "measure" from the "Slicer" table is then placed as a filter on each visual (Show items when the value 'is greater than 0').
See sample images below(not enough historical data to go back 6 months in most cases but it will work in the future)
Thanks also to MFelix for taking the time to investigate.
- MFelix
Super User
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.- MFelix
Hi testingname ,
I refer to the Calculation you have already performed, or are you just adding the columns directly without any measure?
