Forum Discussion

Heinrich's avatar
Heinrich
Post Partisan
1 year ago
Solved

Limitations getting Data from Direct Query to Local

Hello Not all data is copied from Direct Query to Local. The copy works but there is no way the get all the data. I can not use Dataflows or SQL Database. Could it be possible to get only the new...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi Heinrich ,

    1. DirectQuery connections can be restricted in the following ways:

    Row-level filters applied at the data source level. Row/column limitations in the view/query that the DirectQuery model is based on. Data source may only expose recent data. Power BI itself does not limit DirectQuery by date, but it only queries what is needed for the current visual/page.

    2. Verify the True Range of DirectQuery Data

    A. Create a summary measure or table. Use DAX to detect the min/max dates in the DirectQuery table.

    Min Date = CALCULATE(MIN('Table'[Column]))
    Max Date = CALCULATE(MAX('Table'[Column]))

    Note: Place this in a card visual or a table to show the actual temporal range of your DirectQuery data.

    B. Build a simple DirectQuery matrix

    Create a matrix like this: Rows: Year, Month from the date field & Values: Count of rows. It will visually show if older data (e.g. 2024 or pre-May 2025) is missing.

     

    If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

    Thank you

     

pankajnamekar25's avatar
pankajnamekar25
Super User
1 year ago

Hello Heinrich 

Using Composite Model & Manual Append Logic

If your model supports composite mode,

Keep your main table in DirectQuery.

Create a new table with filtered import of "newest data" as mentioned above.

Use DAX to create measures or append tables that reference only the import portion when needed (e.g., for faster visuals, small summary views, etc.).

 

 

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

Heinrich's avatar
Heinrich
Post Partisan
1 year ago

Hello pankajnamekar25 
Hello BhavinVyas3003 
Thank you for your input. 
I can not change the mode. The only solution would be to copy the data. Managed to copy the data but it does only copy 2024.

 

I created the table using a DAX

NER = SUMMARIZE(
'CQD',
'CQD'[Start Time],
'CQD'[PSTN NER Good Percentage],
'CQD'[PSTN Trunk FQDN]
)

And it does only copy 2024.

What do you mean with incremental refresh. The imported table exists but it seems that only 2024 is imported.
Regards
Heinrich