PowerBI - Direct Query, Cache, and Composite Model

Here's the step:

1. create "pbi_test_import" in Azure SQL, Connect PowerBI to it as "import"

create view pbi_test_import
as
select '1' as id, 10000 as output
union all
select '2' as id, 10000 as output
union all
select '3' as id, 10000 as output
union all
select '4' as id, 10000 as output
union all
select '5' as id, 10000 as output
union all
select '6' as id, 10000 as output
union all
select '7' as id, 10000 as output

2. Create another view "pbi_test_live", connect PowerBI as DirectQuery

create view pbi_test_live
as
select '1' as id, 'Brown' as username
union all
select '2' as id, 'Bear' as username

3. Do not set relationship between the 2 table.

4. Create column with following formula on "pbi_test_import" table

Name of User = LOOKUPVALUE(pbi_test_live[username],pbi_test_live[id],pbi_test_import[id] )

 

5. Create table viz from "pbi_test_import"

6. Alter "pbi_test_live" view to add new record

alter view pbi_test_live
as
select '1' as id, 'Brown' as username
union all
select '2' as id, 'Bear' as username
union all
select '3' as id, 'kong' as username

 

7. Use Performance Analyzer -> Refresh visuals

Notice the DirectQuery viz table is refreshed with 3 rows now, but the table viz from imported table is not refreshed

 

8. Perform dummy formula modification.

for example add spaces at the end, any dummy modification is fine.

Name of User = LOOKUPVALUE(pbi_test_live[username],pbi_test_live[id],pbi_test_import[id]              )

 

9. Notice the table viz for imported table is now showing 3 rows.