DAX efficiency: Lookup or concatenate from text?

[TM_Visual]

This is an issue I already have a solution to, but I'd be interested to know which of the possible solutions are most efficient.

I have a table with 1 million rows. One of the values is a date key, a pure integer. I require a column that shows this date as a date. I want to keep the original column.

RecordID	DateKey	DateAsDate
1	20111024	24 October 2011
2	20180115	15 January 2018
3	20140520	20 May 2014
4	20111201	01 December 2011
5	20111025	25 October 2011

What would be quicker/more efficient?

1. A calculated column with the following DAX:

DateAsDate = 

var __DateKey = 'FactTable'[DateKey]
var __day = RIGHT( __DateKey ,2)
var __month = MID( __DateKey ,5,2)
var __year = LEFT( __DateKey ,4)
var __Date = __month & "/" & __day & "/" & __year
return
__Date 

2. A calculated column with a LOOKUP / RELATED formula in DAX, linking to a Dimension table that can match up the datekey to a date.

DateAsDate = RELATED( 'Dim DateInitialDecision'[CalendarDate])

3. Making the change through similar methods such as a merge in the query editor (it's possible, but is it worth it?)

[JarroVGIT]

To be honest, I would imagine that an expression without a JOIN (like the first one) would evaluate quicker than the a statement with a JOIN (like the second one with RELATED). But, the proof is in eating the pudding and I would recommend to test out both and use the performance analyzer (might need to be turned on under Preview settings):

When you hit "Start recording" and create the columns, you can see which is faster. You can't test the third one (doing it in M) but you could clock a full refresh with and without additional M code to compare performance degredations. 

[TM_Visual]

Thanks for the reply.

Unfortunately the Performance Analyzer only works in the Report pane, and not the Data pane.

I made a table visual in the report pane and experimented changing the formula back and forth to see how the times changed.

Sadly it was quite inconsistent, and the ms times varied considerably even without any changes!

The performance difference was not noticeable, and so I guess that it's not the most important issue.

[JarroVGIT]

If you want to measure how long a calculated column takes to evaluate, you don't have to be in date view? Just see how long the evaluation takes when hitting Enter?

[TM_Visual]

If I go to a blank page and change the calculated column code back and forth between the versions, I simply get the message 'changed the model'.

Then if I create a blank page with only a table visual with a single field, I get timings of

Concat method ( RIGHT , LEFT , MID ) : 507, 575, 525

RELATED method: 398, 694, 664

In both cases an examination of the query code shows that it is being affected by the Filters that apply to all pages (e.g. 'person status' is not deceased). However the same filters applied to both techniques.

🤔

Additional fun fact: In Power BI and the Query Editor, if the type of our style of DateKey (e.g 20190513 for the 5th May 2019) is changed from a number to a date, it won't recognise the value as a date format. However, if I change it in the query editor from number to text, and then to date, it recognises it just fine. 

In any case, this doesn't matter that much, but it has been an interesting exercise.