Data modelling - from source or DAX

[clay_75]

Hi all, 

New to BI - trying to work out best approach for the following scenario. 

My main metrics table in report includes metrics such as:

Pick travel time - it uses a date time stamp to compare the previous row (LAG) to the current time stamp

CASE WHEN LAG(AL.TaskDetail_ACTION_DATE_TIME) OVER (PARTITION BY AH.TaskHeader_REFERENCE ORDER BY TaskDetail_ACTION_DATE_TIME) IS  NULL THEN
		DATEDIFF(SECOND, AH.TaskHeader_START_DATE_TIME, AL.TaskDetail_ACTION_DATE_TIME)
ELSE 
		DATEDIFF(SECOND, LAG(AL.TaskDetail_ACTION_DATE_TIME) OVER (PARTITION BY AH.TaskHeader_REFERENCE ORDER BY TaskDetail_ACTION_DATE_TIME),
							AL.TaskDetail_ACTION_DATE_TIME) 
END AS [Seconds To Pick],

It also includes a calculated "travel type category" e.g. changed ailse, first pick, same location, changed level (there's currently 8 categories)

My question - should i be:

1. creating a "travel type category sql view" (with an unknown for accidental additions in the future that aren't catered for), OR

2. creating a "travel type category " table in power bi (DAX) 

3. simply return the value in my metrics table and don't worrry about creating a 'dimension' at all (my value is good enough - i don't think i need an addition description column or anything in my dimension)

I'm sure it depends - but what is best practice (generally 🙂 )

I'm guessing number 1 the sql view is best but has the issue of always keeping travel cats and in metrics table and travel cats view in sync?

But i'm not sure either - my (limited) understanding of vertipaq is that it's very good at grouping a column with low cardinality.

Thanks for any assistance.

Clay

 table

[Anonymous · Accepted solution]

Hi @clay_75 ,

Regarding your question, if your dataset is large. Then creating SQL views, i.e. preprocessing at the data source, is beneficial for performance.Also this is handy if you want to use the same calculation logic in multiple reports.Other than that, I'd use DAX because it's more flexible.