Forum Discussion
Filter data using selected value in slicer
Hello everyone!
I have been trying to do something that seems simple but I cannot achieve it.
I have a table that I use for a slicer and it allows the user to choose the month. The column MonthName is in the slicer.
| MonthName | MonthValue |
Current | 3 |
Jan | 1 |
Feb | 2 |
| Mar | 3 |
| Apr | 4 |
I have another table Data that contains the data that I am using:
| ID | Month | Value |
| A | 1 | 22 |
| A | 2 | 355 |
| A | 3 | 76 |
| A | 4 | 54 |
What I want is to show values of Data when the Month of the Value is less or equal to the Month that the user chose in the slicer (I need the table Months separate because I have a 'Current month' selection possibility'). Here is the measure that I created:
Measure =
VAR monthSelected = SELECTEDVALUE(MonthSlicer[MonthValue])
RETURN
CALCULATE(SUMX(Data,[Value]), Data[Month]<=monthSelected)The expected output is if the User chooses the Month 3:
| ID | Value |
| A | 22 |
| A | 355 |
| A | 76 |
What I get with this measure is only the line that has Month=3 and not the lines that have Month=1 and Month=2.
I guess it has something to do with the context of the filters on the visual.
In the previous case, the slicer is filtering the visual table that show the output.
When I remove this filtering, the measure works only if I put a fixed number (e.g. 3) instead of SELECTEDVALUE.
Thank you for
- You can create a new measure for Value that removes the month filter and then reapplies based on how you want, so
VALUE = CALCULATE(SUM(table[value]), ALL(Month), DataMonth< Selected Month)
Pardon my terrible syntax, that's just meant to provide theory. Let me know if it doesn't make sense.
6 Replies
- Is there any relationship between your Month table and Data table? If there is, it will only ever show the month that is selected, so you'll need to remove the relationship or create a series of measures that remove the filter.
- You can create a new measure for Value that removes the month filter and then reapplies based on how you want, so
VALUE = CALCULATE(SUM(table[value]), ALL(Month), DataMonth< Selected Month)
Pardon my terrible syntax, that's just meant to provide theory. Let me know if it doesn't make sense.
Anonymous , I am hoping both tables are joined. It is a number use less than equal to option in the slicer. With small down arrow in slicer you have that option
