Forum Discussion
Converting calculated column into parameter
I have calculated column which is dependent on a measure. The measure itself is dependent on parameter. Now I want to convert the calculated column into measure form so that when the parameter updates "DaysInMarket" also gets updated. Suggest a solution please.
Calculated Column:
DaysInMarket =
VAR CurrentConsolidatedID = 'Work'[ConsolidatedID]
-- Earliest date for this ConsolidatedID
VAR First_Date =
CALCULATE(
MIN('Work'[Date]),
'Work', 'Work'[ConsolidatedID] = CurrentConsolidatedID &&
Repost Count ID check>0
)
-- Latest date for this ConsolidatedID
VAR Last_Date =
CALCULATE(
MAX('Work'[Date]),
'Work', 'Work'[ConsolidatedID] = CurrentConsolidatedID &&
Repost Count ID check >0
)
RETURN
if ( 'Work'[Date]=Last_Date, DATEDIFF(First_Date, Last_Date, DAY),0)
Measure:
Repost Count ID check =
VAR Threshold = 'Parameter 2'[Parameter Value]
RETURN
CALCULATE(
DISTINCTCOUNT('Work'[id]),
FILTER(
ALLEXCEPT('Work','Work'[ConsolidatedID]),
VAR Previous_Date =
CALCULATE(
MIN('Work'[Date]),
ALLEXCEPT('Work', 'Work'[ConsolidatedID])
)
VAR Initial_Mileage =
CALCULATE(
MIN('Work'[Mileage]),
ALLEXCEPT('Work', 'Work'[ConsolidatedID]),
'Work'[Date]= Previous_Date
)
VAR BaselineID =
CALCULATE(
MIN('Work'[id]),
ALLEXCEPT('Work','Work'[ConsolidatedID]),
'Work'[Date] = Previous_Date
)
VAR DIF= ABS('Work'[Mileage]-Initial_Mileage)
VAR Count_1=CALCULATE(COUNTROWS('Work'), ALLEXCEPT('Work','Work'[ConsolidatedID]))
RETURN
DIF <= Threshold &&
Count_1 > 1
)
)
Hi,
A calculated column won’t react to a parameter change because it’s only evaluated at refresh time. To make DaysInMarket update dynamically when Parameter 2 changes, you need to convert it into a measure and compute First/Last dates using a row-level condition (based on the parameter), then return the value only for the “last date” row (same behavior as your column).
Here’s a working pattern:
1) Create a row-level pass/fail measure (parameter-driven):
Pass Threshold :=
VAR Threshold = SELECTEDVALUE('Parameter 2'[Parameter Value], 0)VAR PrevDate =
CALCULATE(
MIN('Work'[Date]),
ALLEXCEPT('Work', 'Work'[ConsolidatedID])
)VAR InitialMileage =
CALCULATE(
MIN('Work'[Mileage]),
ALLEXCEPT('Work', 'Work'[ConsolidatedID]),
'Work'[Date] = PrevDate
)VAR CountRowsCID =
CALCULATE(
COUNTROWS('Work'),
ALLEXCEPT('Work', 'Work'[ConsolidatedID])
)VAR Dif = ABS( MAX('Work'[Mileage]) - InitialMileage )
RETURN
IF( CountRowsCID > 1 && Dif <= Threshold, 1, 0 )2) Replace the calculated column with this measure:
DaysInMarket :=
VAR CurrentDate = SELECTEDVALUE('Work'[Date])VAR ValidRows =
FILTER(
ALLEXCEPT('Work', 'Work'[ConsolidatedID]),
[Pass Threshold] = 1
)VAR FirstDate = MINX(ValidRows, 'Work'[Date])
VAR LastDate = MAXX(ValidRows, 'Work'[Date])RETURN
IF(
NOT ISBLANK(CurrentDate) && CurrentDate = LastDate,
DATEDIFF(FirstDate, LastDate, DAY),
0
)This will now recalculate dynamically whenever the DaysInMarket parameter/threshold changes. It works best in a table/matrix where Work[Date] is in the visual (so the measure can identify the “last date” row).
3 Replies
Hi,
A calculated column won’t react to a parameter change because it’s only evaluated at refresh time. To make DaysInMarket update dynamically when Parameter 2 changes, you need to convert it into a measure and compute First/Last dates using a row-level condition (based on the parameter), then return the value only for the “last date” row (same behavior as your column).
Here’s a working pattern:
1) Create a row-level pass/fail measure (parameter-driven):
Pass Threshold :=
VAR Threshold = SELECTEDVALUE('Parameter 2'[Parameter Value], 0)VAR PrevDate =
CALCULATE(
MIN('Work'[Date]),
ALLEXCEPT('Work', 'Work'[ConsolidatedID])
)VAR InitialMileage =
CALCULATE(
MIN('Work'[Mileage]),
ALLEXCEPT('Work', 'Work'[ConsolidatedID]),
'Work'[Date] = PrevDate
)VAR CountRowsCID =
CALCULATE(
COUNTROWS('Work'),
ALLEXCEPT('Work', 'Work'[ConsolidatedID])
)VAR Dif = ABS( MAX('Work'[Mileage]) - InitialMileage )
RETURN
IF( CountRowsCID > 1 && Dif <= Threshold, 1, 0 )2) Replace the calculated column with this measure:
DaysInMarket :=
VAR CurrentDate = SELECTEDVALUE('Work'[Date])VAR ValidRows =
FILTER(
ALLEXCEPT('Work', 'Work'[ConsolidatedID]),
[Pass Threshold] = 1
)VAR FirstDate = MINX(ValidRows, 'Work'[Date])
VAR LastDate = MAXX(ValidRows, 'Work'[Date])RETURN
IF(
NOT ISBLANK(CurrentDate) && CurrentDate = LastDate,
DATEDIFF(FirstDate, LastDate, DAY),
0
)This will now recalculate dynamically whenever the DaysInMarket parameter/threshold changes. It works best in a table/matrix where Work[Date] is in the visual (so the measure can identify the “last date” row).
Hi mmvohra ,
Thank you mohit_sakhare for the inputs.
We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.
Thank you.Hi mmvohra ,
May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.
Thank you.
