Forum Discussion
VARIABLE: UNDESIRED BEHAVIOR
Hi,
I have a line chart where I want it to show the data only until the selected month. This is the metric that I created with a variable that returns that month's number.
The result of the variable is 4. I check that this is the result, it's numeric format... all it's OK, but the graph shows all months
SeleccionMeses =
VAR MesSeleccionado=SELECTEDVALUE(PBI_AMOUNT[DATE_REF].[NroMes])
RETURN
CALCULATE ([IMPORTE];
ALL (PBI_AMOUNT[DATE_REF].[NroMes]);
PBI_AMOUNT[DATE_REF].[NroMes] <= MesSeleccionado
)
But if change the last line for the result of the variable it works!
PBI_AMOUNT[DATE_REF].[NroMes] <= 4
I have tried creating the variable outside and calling it in the function, that there were no format failures, etc ... but I can't get Power BI to use the result of variable (number 4) correctly.
Please, could someone help me with this issue?
Thanks in advance!!!!!!!
See the attached file for an example. Like I said, this is a quick and dirty solution. You should eschew the auto date/time feature altogether and create your own date table that can be used in the slicer The auto date/time feature can be turned off at
Options and settings->Options-> Current File -> Data Load ->Time Intelligence
and at
Options and settings->Options-> Global -> Data Load ->Time Intelligence
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
7 Replies
Hi,
Thanks for your answer. Attach one simplified example of them.
https://wetransfer.com/downloads/7ebc1653cfa2e7bb7455c2483d7107e620190905084806/b932f6
If you change the last line of the variable for a especific value is the result that I need to get
Hoja1[DATE_REF].[NroMes] <= MesSeleccionado ------> Hoja1[DATE_REF].[NroMes] <= 5
THANKS!
The problem is that you are using Hoja1[DATE_REF].[NroMes] both in the slicer and in the axis of the chart. What the measure reads from SELECTEDVALUE is the value it gets from the axis. Plus there are some other issues with using the date hierarchy, which I would not recommend. Usually, selecting one month on the slicer would mean that only that month is displayed on the chart, since it's the same field on the axis. But that doesn't seem to happen, probably because of what Power BI builds behind the scenes to implement the date hierarchy.
I think the quickest solution would be to create another one-column table with he month numbers and use that as a slicer. The you read that with a SELECTEDVALUE and take the MAX(Slicer, value from axis) to limit the SUM. Make sure the measure returns BLANK if the value in the axis is above the value in the slicer, so that nothing is depicted on the chart.
