FreddySetiawan's avatar
FreddySetiawan
Advocate II
5 years ago
Status:
New

PowerBI - Direct Query, Cache, and Composite Model

HI,

 

I'm building PowerBI model with composite model.

There are some table that's in import mode, and some which are DirectQuery.

 

I created calculated column on the imported table to perform value lookup from DirectQuery table.
But the column only refreshed when I made dummy changes on the formula.

I can't refresh the calculated column value no matter what I do.

 

The use case here is User are performing data mastering on a tool.
The DirectQuery table purpose is to allow us to get the latest mastered value from the tool into the imported table.

We can't convert the DirectQuery to Imported mode as the table refresh frequently.

On the other hand we can't switch the Imported table to DirectQuery because of the complex and large dataset.

 

 

Related community thread discussion: https://community.powerbi.com/t5/Desktop/PowerBI-Direct-Query-amp-Cache/td-p/1303518

Raised Microsoft Support Ticket: 120082226000045

None of them give me good answer.

2 Comments

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    5 years ago

    FreddySetiawan ,

     

    It's hard to reproduce this issue on my side. If your desktop is not the latest version, please try updating to the latest version. In addtion, if you already have the latest version, could you please share the sample data and the calculate columns you have build for further analysis?

     

    Regards,

    Jimmy Tao

     

  • FreddySetiawan's avatar
    FreddySetiawan
    Advocate II
    5 years ago

    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.

Completed Ideas