0

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 )
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
Prem Chand
  • 126
  • 1
  • 10

1 Answers1

0

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.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Actually, I tried to pass measure instead of parameter, but it is not working since there is no relationship between the tables. And I don't want to have a relationship between tables since it is affecting result. – Prem Chand Dec 16 '19 at 18:50
  • The answer is the same. Calculated columns are fixed once they are defined. You can't make them interact with a slicer/filter. – Alexis Olson Dec 16 '19 at 19:05
  • `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)` I have replicated calculated column in measure, but result shows 0 results. @Alexis Olson could you please help me on this to convert to measure – Prem Chand Dec 16 '19 at 20:18
  • I got the result using below function but i am not able to summarize the measure. `var related_year = VALUES(Parameter[Parameter]) 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(related_year,12,31)) var result= if(if_1>if_2,countx(Roles,Roles[Job Title]),0) return result` @Alexis Olson Could you please help me on this. – Prem Chand Dec 16 '19 at 22:01
  • Please don't put extended code in the comments as it's not readable. You can edit your post instead. – Alexis Olson Dec 16 '19 at 22:02