Filter data using selected value in slicer

[Anonymous]

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

[amitchandak]

@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

[AllisonKennedy]

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.

[Anonymous]

Thank you for your reply!

There is indeed a relationship between Data[Month] and Slicer[MonthValue]. However I need it for other visuals as well as in order to have the right values for each ID.

[AllisonKennedy · Accepted solution]

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]

Thank you for your help! That was what was missing! It works now 🙂

[Anonymous]

Hi @Anonymous ，

Glad to know that your issue is solved,could you pls mark the reply as answered to close it?

Much appreciated.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!