Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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
amitchandak
Super User
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?

 

View solution in original post

1 REPLY 1
amitchandak
Super User
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?

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors