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 YearValue
201013
201112
201212
20136
201418
201515
201611
201719
20188
201917
202016

 

i want to create 2 New Calculated Column (Is Future & Age ), i want these fields in Calulated Column (Not Measure), because based on Age field  i want to create data model conection to other table  

 

Example: I selected Year filter from Dim_Calendar = 2016, so based on that i want to calculate Is Future & Age value  

 

Now i selected 2018, so Is Future & Age value want to change based on Dim_Calendar year selection

 

How Can i achive this logic?

 

Thanks,

Vengadesh 

 

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

6 Replies

  • lukiz84's avatar
    lukiz84
    Memorable Member
    3 years ago

    You can't achieve it. Calculated columns are calculated on model refresh and therefore don't know anything about the filter context.

     

    What you can do is to use some iterator functions like SUMX. What should the output be and where?

  • 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