I only try to convert when I know the data in ReferenceValue is convertible to INT by using the WHERE ReferenceType = clause.

Currently the conversion sometimes fails even when I...

I posted a follow-up an hour ago but I don't see it (yet)

Here's my latest,  added CROSS JOIN to DISTINCT  AccountID which returns the correct number of records.

My 'lag' columns...

Good Morning or Good Evening,

Long thread here so I thought I would simplify the problem and my questions.

When limiting the account to 1 (AccountID = 1) I get 30 records....

Hello Again,

Funny when you search for an answer and find your own thread. I added some additional fields to my data to make it more real world and also data-typed...

Yes I went down that path. See below. The CASE is currently excluding my problematic data. The problematic data is actually caused by a data entry error.  When I correct...

Yes, same formula...

• This reply was modified 1 year, 4 months ago by  Chrissy321.

I probably should have stated my use case. It's not a traditional data warehouse but rather a tabular model on which PowerBI reports are built for analysis/business intelligence....

I probably should have stated my use case. It's not a traditional data warehouse but rather a tabular model on which PowerBI reports are built for analysis/business intelligence. My analysts...

• This reply was modified 1 year, 5 months ago by  Chrissy321. Reason: typo

Uh-oh that means I am not following one my best practices which is 'Do what Jeff' recommends'. Any additional color or reasoning on why no fact table updates and what...

I have scars from using MERGE in the past and have since used separate INSERT and UPDATE statements. I haven't revisited MERGE recently, maybe everything is fine now but you...

Yes updating the existing record with the new value.

Air code to follow

UPDATE Fact

SET F.Attribute1 = S.Attribute1

OUTPUT--do some logging

FROM Fact F

INNER JOIN Staging S

ON...

WHERE...--detect data that needs to be corrected

I detect facts that are not 'correct' and the update the fact in place while logging what was changed. I refer to the Kimball books and methods but I am...

Ok looks like mostly formatting. You will probably want to

• right align the right upper 2 expressions
• set the font and font sizes based on what is in crystal
• expand...

Can you show design view like this as an example.

And then a preview of the data like this

June 15, 2023 at 3:07 pm

#4212021

I meant current view of the SSRS report in design view. Also a preview of the data would be helpful.