Create Calculated Column based on filter Selection

[vengadesh_p]

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

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 

[Anonymous]

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]

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]

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]

@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] )

[Anonymous · Accepted solution]

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

[lukiz84]

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?