Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Problems creating calculated table from a slicer

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!
Need Help

11 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        littlemojopuppy
        Community Champion
        4 years ago

        Anonymous 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 😕

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

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

        You can set data color manually.

         

        Best Regards,

        Jay