semantic model can be refreshed successfully, but the calculation does not work anymore

[Feiiiry]

Hi @PowerBI ,

I have a dataflow A in DATA workspace, a semantic model A (connected to this dataflow) also in the DATA workspace.

I created a report connecting to the semantic model A and i published it in the ANALYSIS workspace.

I created two new calculated columns in semantic model A. 

I found out that if i publish the semantic model A directly to DATA workspace without refreshing. All my views, measures and calculated columns work perfectly. HOWEVER, if i refresh the semantic model A after publishing it (I can refresh it successfully), the calculated columns i newly created do not work anymore, with the error msg 'The query referenced calculated column '_Table'[Column] which does not hold any data because evaluation of one of the rows caused an error.'

Here is one of the calculated column i created:

IsDateCreatedLastWeek =

VAR CurrentDate = _Table[DATE_OF_CREATION]

VAR LastWeekDates =

    CALCULATETABLE(

        VALUES(dim_date[Date]),

        USERELATIONSHIP(dim_date[Date], _Table[DATE_OF_CREATION]),

        dim_date[Offset ISO Week] = -1 || dim_date[Offset Day] = 0

    )

VAR LastWeekStartDate = FIRSTDATE(LastWeekDates)

VAR LastWeekEndDate = LASTDATE(LastWeekDates)

RETURN

IF(

    CurrentDate >= LastWeekStartDate &&

    CurrentDate <= LastWeekEndDate,

    1,

    0

)

The issue looks soooooooooooo weird to me. Can anyone help me out? Did anyone have teh same issue before? 

Thankssss

[AmiraBedh]

use TRY to evaluate the CurrentDate and LastWeekDates separately and see if any errors arise.

IsDateCreatedLastWeek =
VAR CurrentDate = _Table[DATE_OF_CREATION]
VAR LastWeekDates =
CALCULATETABLE(
VALUES(dim_date[Date]),
USERELATIONSHIP(dim_date[Date], _Table[DATE_OF_CREATION]),
dim_date[Offset ISO Week] = -1 || dim_date[Offset Day] = 0
)
VAR LastWeekStartDate = FIRSTDATE(LastWeekDates)
VAR LastWeekEndDate = LASTDATE(LastWeekDates)
RETURN
IF (
NOT (ISBLANK(CurrentDate)) &&
NOT (ISBLANK(LastWeekStartDate)) &&
NOT (ISBLANK(LastWeekEndDate)) &&
CurrentDate >= LastWeekStartDate &&
CurrentDate <= LastWeekEndDate,
1,
0
)

Links to help you :

https://community.fabric.microsoft.com/t5/Power-Query/Query-referenced-calculated-table-which-does-n...

https://community.fabric.microsoft.com/t5/Service/The-query-referenced-calculated-column-Table-Colum...

https://community.fabric.microsoft.com/t5/Service/The-query-referenced-calculated-column-Table-Colum...

[Feiiiry]

Hi Amira,

Thanks for your help! it still doesnt work.

i used try and catch, there is no error in the semactic model (when i tried to make a table/bar chart as a test). But it still generates an error 'The query referenced calculated column '_Table'[Column] which does not hold any data because evaluation of one of the rows caused an error.' and / or 'The query referenced column '<oii>LocalDateTable_e1cff804-5c11-409a-b2c2-6ce16587870a6f</oii>'[<oii>Year</oii>] which depends on another column, relationship or measure that is not in a valid state. Additional information: ''.

As long as i dont refresh my semantic model, the calculated columns i created will show/filter everything correctly. Once i refreshed, the same error keeps appearing 😕

So, i just gave up this solution and used another one. It works though not properly ...

Thanks again for your help 😉