Powerbi Calculated Column Parameter value passing with no table relationship

Question

I have a scenario where I need to pass a parameter value in the calculated column measure, and there should not be any relationship between the parameter table and the master table.

Below are the sample calculated column I have created.

Title = 
var related_year = Parameter[Parameter Value]
var date_closed = Roles[Date Closed]+365
var if_1 = if(Roles[Status] = "Open", date_closed,Roles[Date Closed]) 
var if_2 = if(related_year = YEAR(TODAY()),TODAY(),date(related_year,12,31))
return if((if_1>if_2),title,0)

I tried hard coding the parameter value in the calculated column that option is working as expected. But, my expectation is to pass the parameter value with no relationship between tables.

The parameter(Parameter[Parameter Value]) value is not giving the result expected due to filter applied on the table level. And, there is a relationship between the year table and role table based on year value, but I don't want any relationship between table, just to pass the parameter value, since it is affecting my logic.

Please advise me, how to pass the parameter value without any relationship in the model as Tableau, or let me know if any other option available in power bi to solve this scenario.

Edit:

I have replicated the calculated column as a measure, but the result shows 0 results. Could you please help me with this conversion?

Title =
VAR related_year = Parameter[Parameter Value]
VAR date_closed = SELECTEDVALUE ( Roles[Date Closed] ) + 365
VAR if_1 =
    IF (
        SELECTEDVALUE ( Roles[Status] ) = "Open",
        date_closed,
        SELECTEDVALUE ( Roles[Date Closed] )
    )
VAR if_2 =
    IF (
        related_year = YEAR ( TODAY () ),
        TODAY (),
        DATE ( YEAR ( related_year ), 12, 31 )
    )
RETURN
    IF ( if_1 > if_2, DISTINCTCOUNT ( Roles[Job Title] ), 0 )

Some example data and the expected result would be useful as well as screenshot of your current result. — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2019-12-16T17:53:57

A calculated column cannot be dynamically responsive to visuals as it is only calculated at the time the table is loaded.

You need to use measures, instead, for interactive parameters.

Powerbi Calculated Column Parameter value passing with no table relationship

1 Answers