Forum Discussion
Help converting calculated column to measure
Hi everyone, we have a fairly extensive calculated column that computes for the entry TAT of a work order. This works for us but it has come to a point where opening the file takes about an hour or so possibly due to the load the calculated column puts on the machine, apart from the actual volume of data we're working on. That said, is it possible to convert the following calculated column to a measure? I have to admit I'm not as versed in making simple measures (eg: SUM, etc.) but this boggles quite. In our code below, Data and Holiday are related by a Calendar table.
Entry TAT =
VAR vStartDate = 'Data'[RecdDate]
VAR vEndDate =
SWITCH(
TRUE(),
ISBLANK('Data'[EntryDate]) = FALSE(), 'Data'[EntryDate],
'Data'[RecdDate]
)
// Swap dates is vStartDate is later than vEndDate
// Create calendar based on dates
VAR vCalendar = CALENDAR(MIN(vStartDate, vEndDate), MAX(vStartDate, vEndDate))
VAR vWeekdays = ADDCOLUMNS(vCalendar, "Weekday", WEEKDAY([Date]))
VAR vTAT =
IF(vStartDate = vEndDate, 0, //Same day processing
SWITCH(
TRUE(),
'Data'[WeekendType] = 2, COUNTX(FILTER(vWeekdays, AND([Weekday] <> 6, [Weekday] <> 7)), [Date]) - 1, // Fri, Sat
'Data'[WeekendType] = 3, COUNTX(FILTER(vWeekdays, AND([Weekday] <> 5, [Weekday] <> 6)), [Date]) - 1, // Thu, Fri
'Data'[WeekendType] = 4, COUNTX(FILTER(vWeekdays, AND([Weekday] <> 6, [Weekday] <> 1)), [Date]) - 1, // Fri, Sun
'Data'[WeekendType] = 5, COUNTX(FILTER(vWeekdays, [Weekday] <> 1), [Date]) - 1, // Sun
'Data'[WeekendType] = 6, COUNTX(FILTER(vWeekdays, [Weekday] <> 6), [Date]) - 1, // Fri
'Data'[WeekendType] = 7, COUNTX(FILTER(vWeekdays, [Weekday] <> 7), [Date]) - 1, // Sat
COUNTX(FILTER(vWeekdays, AND([Weekday] <> 7, [Weekday] <> 1)), [Date]) - 1 // Sat, Sun
)
)
VAR Holidays =
COUNTROWS(
FILTER(
'Holiday',
'Holiday'[Date] >= vStartDate &&
'Holiday'[Date] <= vEndDate &&
'Holiday'[IsWeekend] = 0 &&
'Holiday'[Country] = 'Data'[Country]
)
)
RETURN IF(vTAT - Holidays < 0, 0, vTAT - Holidays)
4 Replies
Hey olimilo ,
the question is, what are you using the column for in your report?
Calculated columns and measures are two different concepts in DAX and with a measure you cannot do everything you can do with a calculated column.
Are you for example using the calculated column to filter the report? This would not be possible with a measure as a measure always needs a filter context.
When it takes too long to create that calculated column in Power BI, could you "outsource" that expensive task to your data source? Maybe add a new column in the view. An SQL Server usually has more power to calculate than your notebook. Could you calculate that table in data flows? It could run overnight, you wouldn't mind if it takes an hour. And when you connect to your data flow table the data is ready immediately.
Just want to give you some ideas if a measure is the right solution for your problem or if another alternative would be better.
If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicHi Denis selimovd,
We're actually working with flat files on this one so all of the processing is done in the desktop client/file itself.
What we're trying to do with this calculated column is simply to calculate for the entry TAT, without need to filter the data with it which I think would work best with measures.
