Thanks for the links Jeff. I am going to put PIVOT in the 'do not use' receptacle where it can hang out with MERGE. 🙂

Thank you both. Moving away from PIVOT and using the conditional aggregation method clearly gives me more flexibility to solve the problem and also seems to be performing better.

Can you contact the mail administrator? Intermittent mail failures can happen if there is smtp load balancing going on and you get sent to a mail server that is having...

Have you seen these links?

https://dba.stackexchange.com/questions/200367/can-ssrs-used-to-update-or-modify-data-tables

https://stackoverflow.com/questions/38583661/can-i-modify-data-by-using-ssrs

Sounds like you want a CRUD application.

CREATE means adding or inserting rows into a table.

READ means selecting (retrieving) rows from a table.

UPDATE means modifying...

How are you issuing the processing command? Are you using a SQL Server job with Type: SQL Services Analysis Services Command?

I dimly recall that error but I haven't seen it...

Yes, adding a date filter to my where clause seems to create a new plan causing the conversion error.

The Convert to VARCHAR seems reliable but imposes a performance penalty.

A better...

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, 7 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, 7 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...