Problems creating calculated table from a slicer

[ccalobeto]

Hello,

I've been stuck with this issue for hours and I need help understanding why this solution isn't working. 

What I want to solve is this: 

I have a large table and based on a dropdown isolated slicer select a single year to filter the Prep_Sales (2) table to deliver only the valid rows for the present year and the past year. Also, I want to display the filtered data in a line chart so I need some calculated columns and a relationship to my hierarchy table.

But for some reason, the slicer didn't get the year (on CurrentYear VAR) even the filter works if you put it manually. I made sure that Year is an integer value.

My script is the following 

Calculated Table from Prep_Sales (2) =

VAR CurrentYear = SELECTEDVALUE('Years (slicer)'[Year])

RETURN

FILTER(

'Prep_Sales (2)',

'Prep_Sales (2)'[Year]= CurrentYear ||

('Prep_Sales (2)'[Year]= CurrentYear - 1 && 'Prep_Sales (2)'[CategoryAppend] = "Results")

)

My model 

The report 

Properties of the report

 

 

 Please, help me solve this!

[v-jayw-msft]

Hi @ccalobeto ,

You could create a measure like below.

measure =
var sales_year = selectedvalue('Prep_Sales (2)'[Year])
var CurrentYear = SELECTEDVALUE('Years (slicer)'[Year])

return
IF( sales_year = CurrentYear || sales_year = CurrentYear-1,1,0)
Add this measure to visual filter set value = 1.

Best Regards,

Jay

[ccalobeto]

I took it. But I can't maintain the colors because the category is on the legend.

So I have to get deep into this because I need some time calculations and Dax patterns to improve the design, and I think it is the right way to do this.

Thank you 

[v-jayw-msft]

Hi @ccalobeto ,

"But I can't maintain the colors because the category is on the legend."

You can set data color manually.

Best Regards,

Jay

[ccalobeto]

Sharing the pbix file.

I want the things :
- Select a Year and a Metric

- The Line Chart will display the metric and year selected with 3 curves: Present Year, Quota, and Last Year.

- Keep the Line colors when you change the metric or year slicers, for example, red for Quota, blue for Present Year, and gray for Last Year.

https://drive.google.com/drive/folders/1NyodJ-pBQL2DQXk8XzChm4e0mimO4Nca?usp=sharing

Thanks for your time and patience,

Carlos

[littlemojopuppy]

@ccalobeto are you saying that the calculated table doesn't recalculate based on slicer selection?

[ccalobeto]

Yes. I've tried with SELECTEDVALUE('Years (slicer)'[Year]), INT(SELECTEDVALUE('Years (slicer)'[Year])) and CONVERT(SELECTEDVALUE('Years (slicer)'[Year]), INTEGER) but didn't work. Any light with this BI variable?

[littlemojopuppy]

@ccalobeto calculated tables and columns only recalculate when data is refreshed, not when slicers change.  What you're trying to do is not possible.  Sorry to be the bearer of bad news 😕

[ccalobeto]

Thanks for your time. It seems that I will change something of this model. How can I handle this solution?

[littlemojopuppy]

@ccalobeto no worries...I'm just trying to run out the clock at work.  What are you trying to do?

[ccalobeto]

I want to do these things:

- Select a Year and a Metric 

- In a Line Chart display the metric selected with 3 curves: Present Year, Quota, and Last Year (see the image above).

- Keep the Line colors when you change the metric or year, for example, red for Quota, blue for Present Year, and gray for Last Year.

Thanks

[littlemojopuppy]

@ccalobeto can you provide sample data in a pbix file to work with?