Forum Discussion
Incremental Refresh from TE3 Gets Nebulous Type Conversion Error
This verifies what the problem is. By running this simple DAX query in TE3, I can reproduce the exact same error I was getting:
So, the answer here is, when employing this DAX-based dimDate table and refreshing an "empty" model (metadata-data only / freshly-deployed), to be sure to:- refresh a partition that you are sure has data in the source, first, before the other partitions
- include some error-trapping code in the DAX that accounts for instances where it might return an empty string (BLANK())
I've said it many times and I'll say it again. Creating calendar tables in DAX or Power Query is futile. Those tables are immutable and should be sourced further upstream. You should also never combine Incremental Refresh with Auto Date/Time - exactly to avoid these kind of post refresh computations and to avoid refreshes of related partitions.
Well, I vehemently disagree. I know that a DAX-based calendar table can cause some issues sometimes, but the juice is worth the squeeze (IMHO).
- they are light-weight (data as code), they are dynamic (always the exact records that you need, not some SQL-based giant that needs stored procs to update it every couple years)
- their logic is included in source control with their associated model (not in the source control for the data warehouse)
- their logic can quickly and easily be changed by the BI professional with no arguing or waiting on data engineers to add / fix things
- that logic / features can differ from model to model (a table in the DW can't unless you give it a hundred columns)
- having the calendar logic contained within the Power BI environment alongside other model logic can simplify management and troubleshooting, as everything is managed in a unified manner.
But I certainly respect your opinion and your approach.