Forum Discussion
auto-update data from SQL Server
Create a view on your SQL server that does a UNION ALL on the two tables.
Creating a union in DAX will result in a calculated table which behaves like import mode.
I have my data in two semparated tables (Logger 1 and Logger2) within an SQL Server DB. This is connected with PowerBI Desktop through DirectQuery. For both tables the power BI Automatic Page Refreh (APR) works fine. However in order to use a slicer and other techniques (measures, calculation etc.), I need to have the data of the two tables in the same table. For this reason I have created a new table as "Union(logger1, logger2)".
My problem is that, even thogh this table is directly connected to the two tables which update automatically, this table does not update automatically at all.
PS.: In order for this composite model to work, I have also activated the "DirectQuery for Power BI dataset and Analysis Services" option from settings.
Create a view on your SQL server that does a UNION ALL on the two tables.
Creating a union in DAX will result in a calculated table which behaves like import mode.
I have doen what you suggested and it works, but in the sql sintax I have edited I have problem in identifying the new logger's data.
1. I have updated the tables adding a new column (ALTER TABLE Logger1, ADD LOGGER varchar(10);
2. I have updated the new column, giving the name of the logger (UPDATE Logger1 SET LOGGER = 'RT1')
3. I have created the new union table (SELECT * INTO LOGGERS FROM
(SELECT * FROM Logger1
UNION
SELECT * FROM Logger2) aSELECT * FROM LOGGERS
The old data are correctly identified as RT1 or RT2, but the new data are Blank! I wish I can have the old and the new data identified automatically as RT1 or RT2. Have you hints to fix this?When you SELECT INTO you are materializing the view. Are you sure you want that?
Your view definition should be:
CREATE VIEW Loggers AS SELECT *,'RT1' [Source] FROM Logger1 UNION ALL SELECT *,'RT2' FROM Logger2