cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Variable Performance

Hi all,

I have a calculated column and need to create two more columns based on same Variable. As we cannot re-use variables, which option is best in terms of performance/clearness of coding

Option 1. Create the two columns with same variable in each one

Option 2. Add a variable into the first calculated column, so I can use this in the new two columns -therefore, no need to rewrite the same variable 3 times

Option 3. New ideas welcome!

Example Option 1

Calculated column 1:

Days_late = DATEDIFF('Table'[Due_date], TODAY(), Day)

Calculated column 2:

Late = VAR days_late = DATEDIFF('Table'[Due_date], TODAY(), Day)

RETURN
SWITCH(TRUE(),
days_late = 0, "Due today",

AND(days_late>0, days_late < 15), "Late Less 15 days"

days_late >=15, "Late More 15 days"

"On Time"

)

Calculated column 3:

On Time = VAR days_late = DATEDIFF('Table'[Due_date], TODAY(), Day)

RETURN
SWITCH(TRUE(),
AND(days_late<0, days_late >-15, "Due in Less 15 days",

days_late<=-15, "due in More 15 days"

"Late"

)

Example Option 2

Calculated column 1:

Days_late = VAR days_late = DATEDIFF('Table'[Due_date], TODAY(), Day)
RETURN
days_late

Calculated column 2:

Late = VAR days_late = DATEDIFF('Table'[Due_date], TODAY(), Day)

RETURN
SWITCH(TRUE(),
days_late = 0, "Due today",

AND(days_late>0, days_late < 15), "Late Less 15 days"

days_late >=15, "Late More 15 days"

"On Time"

)

Calculated column 3:

On Time = VAR days_late = DATEDIFF('Table'[Due_date], TODAY(), Day)

RETURN
SWITCH(TRUE(),
AND(days_late<0, days_late >-15, "Due in Less 15 days",

days_late<=-15, "due in More 15 days"

"Late"

)

Thank you for your time and support!

1 ACCEPTED SOLUTION
Super User

When Calculated Days_late as the column, you can use the same in other columns

My suggestion would be this

``````Calculated column 1:

Days_late = DATEDIFF('Table'[Due_date], TODAY(), Day)

Calculated column 2:

Late =
SWITCH(TRUE(),
[Days_late] = 0, "Due today",
AND([Days_late]>0, [Days_late] < 15), "Late Less 15 days"
[Days_late] >=15, "Late More 15 days"
"On Time"
)

Calculated column 3:
On Time =
SWITCH(TRUE(),
AND([Days_late]<0, [Days_late] >-15, "Due in Less 15 days",
[Days_late]<=-15, "due in More 15 days"
"Late"
)``````

Is there a need for a third. Can they 2nd and 3rd can merge?

Super User

When Calculated Days_late as the column, you can use the same in other columns

My suggestion would be this

``````Calculated column 1:

Days_late = DATEDIFF('Table'[Due_date], TODAY(), Day)

Calculated column 2:

Late =
SWITCH(TRUE(),
[Days_late] = 0, "Due today",
AND([Days_late]>0, [Days_late] < 15), "Late Less 15 days"
[Days_late] >=15, "Late More 15 days"
"On Time"
)

Calculated column 3:
On Time =
SWITCH(TRUE(),
AND([Days_late]<0, [Days_late] >-15, "Due in Less 15 days",
[Days_late]<=-15, "due in More 15 days"
"Late"
)``````

Is there a need for a third. Can they 2nd and 3rd can merge?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.