Forum Discussion

nbarsley's avatar
nbarsley
Helper I
1 year ago
Solved

Union Power BI Service Datasources

Hi there! I've got a large dataset that is somewhat slow to refresh and my team would like the data updated every 30 minutes. I've been thinking about how I can cache the stale data in one dataset i...
Dataflow
Need Help
  • Akash_Varuna's avatar
    Akash_Varuna
    1 year ago

    Hi nbarsley Could you follow these please 

    • Publish both stale (daily) and current (30-minute) datasets to the same Power BI workspace.

    • In Power BI Desktop, use Get Data > Power BI Datasets to connect to both datasets. Build a composite model by querying both.

    • Create a calculated table using DAX:

      CombinedData = UNION(Dataset1[TableName], Dataset2[TableName])

    • Use the CombinedData table to build your report visuals.

    • Publish the combined model to the Power BI Service for dynamic updates.

    If this post helped please do give a kudos and accept this as a solution 
    Thanks In Adavnce

     

Akash_Varuna's avatar
Akash_Varuna
Super User
1 year ago

Hi nbarsley Could you follow these please 

  • Publish both stale (daily) and current (30-minute) datasets to the same Power BI workspace.

  • In Power BI Desktop, use Get Data > Power BI Datasets to connect to both datasets. Build a composite model by querying both.

  • Create a calculated table using DAX:

    CombinedData = UNION(Dataset1[TableName], Dataset2[TableName])

  • Use the CombinedData table to build your report visuals.

  • Publish the combined model to the Power BI Service for dynamic updates.

If this post helped please do give a kudos and accept this as a solution 
Thanks In Adavnce

 

  • nbarsley's avatar
    nbarsley
    Helper I
    1 year ago

    Thanks, 

    I did try this earlier but received this error message:

     

    Something went wrong

    The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.

  • nbarsley's avatar
    nbarsley
    Helper I
    1 year ago

    Can you confirm that this works with more than 1,000,000 rows? It doesnt work for me. 

    • v-nmadadi-msft's avatar
      v-nmadadi-msft
      Community Support
      1 year ago

      Hi  nbarsley ,
      Thanks for reaching out to the Microsoft fabric community forum.

      It is not dependent on rows but instead on the storage size of semantic models.
      The maximum size for semantic models imported into the Power BI service is 1 GB. These semantic models are heavily compressed to ensure high performance. In addition, in shared capacity, the service places a limit of 10 GB on the amount of uncompressed data that is processed during refresh. This limit accounts for the compression, and therefore is higher than the 1-GB maximum semantic model size. Semantic models in Power BI Premium aren't subject to these limits. If refresh in the Power BI service fails for this reason, reduce the amount of data being imported to Power BI and try again.
      Source: Troubleshoot refresh scenarios - Power BI | Microsoft Learn

      If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS.
      Thanks and Regards