Forum Discussion

vengadesh_p's avatar
vengadesh_p
Helper II
3 years ago
Solved

Create Calculated Column based on filter Selection

Hi All,   I have data like below table Period Year Value 2010 13 2011 12 2012 12 2013 6 2014 18 2015 15 2016 11 2017 19 2018 8 2019 17 2020 16  ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi vengadesh_p ,

    Please update the formula of measure [00_Category] as below and check if it can return the correct result. Please find the details in the attachment.

    00_Category = 
VAR _age = [00_Age]
RETURN
    MAXX ( FILTER ( 'Age Category', 'Age Category'[Age] = _age ), [Category] )

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi vengadesh_p ,

As the value of a calculated column is computed during data refresh and uses the current row as a context, it will not change according to the user interaction(slicer, filter, column selections etc.) in the report. So you can't get the expected result by creating calculated column just as what lukiz84  said. Please review the following links about the difference of calculated column and measure...

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

 

Could you please provide some sample data in your tables (exclude sensitive data) with Text format and your final expected result with backend logic and special examples. Later we can take a look at the above info and check if there is another alternative can achieve it... Thank you.

Best Regards

  • vengadesh_p's avatar
    vengadesh_p
    Helper II
    3 years ago

    Hi Anonymous 

    This Is My datamodel , I have connection Age Category to Dim Calendar table by using Age Field 

    This Age field want to calculate based on Refernce Year parameter 


    Example : i selected 2020 so Age field want to change based on 2020 & based on Age field i want to show data from Age Category Table

     

     

    Note: Currently i have achived this logic by static Value (_Selected Year = 2020) this selected year value want to change dynamically by reference year selection 


    I have attached Sample Pbix File  

     

    Thanks

    vengadesh 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi vengadesh_p ,

      I updated your sample pbix file(see attachment), please check if that is what you want. You can create three measues as below to get it:

      00_IsFuture = 
IF (
    SELECTEDVALUE ( 'Dim_Calendar'[Year] ) <= [_Reference Year Value],
    "Actual",
    "Future"
)
      00_Age =
IF (
    SELECTEDVALUE ( Dim_Calendar[Year] ) > [_Reference Year Value],
    0,
    [_Reference Year Value] - SELECTEDVALUE ( 'Dim_Calendar'[Year] )
)
      00_Category =
MAXX ( FILTER ( 'Age Category', 'Age Category'[Age] = [00_Age] ), [Category] )

      Best Regards

      • vengadesh_p's avatar
        vengadesh_p
        Helper II
        3 years ago

        Anonymous 
        00_Category was not working if i select 2019 or 2018.. etc 

        00_Category =
MAXX ( FILTER ( 'Age Category', 'Age Category'[Age] = [00_Age] ), [Category] )