Data not visible in the Service after publishing Dataset

[Marijan]

Hello everyone

I'm curently struggeling with an issue in my Power BI report. My issue is that after publishing my dataset to the service some data are not visible.

Explanation of my setup:

I have setup a dataset for my report. The data in my dataset are coming from a SQL database. I use the import option to load my data from the SQL database to the dataset. I import multiple tables which are linked together. My main table is the order table from my SQL database.

To reduce the loading time I use in the Power Query the Join option to merge tables before I load the data into the dataset and later for puplishing the report from the desktop I use the option incrementel refresh to load and refresh only a specific timerange of data.

For the join typ I combine two tables with "Iner join". For example the order table with the cost table. The idea is to show only cost positions which are matching to the order table.

For the incremental refresh I have created range filters for one month. The range filters are used in the table orders. In the incremental refresh settings I have configured it to refresh only data from the actual year and the last 2 years.

Vizual:

I have one vizual which contains data from the order table and the cost table. Its basicly a matrix list which shows orders with cost positions.

Issue:

After publishing the dataset from the desktop to the service and after doing the first refresh, only the data from the order table are visible in all months. The data from the order cost table are only visible for the month wich is defined in the rangefilter. But if I understand it correctly, the incremental refresh should dynamicly change the range filter with my defined paramters in the settings (which are actuall year and last 2 years). Then building on the join option, it should load only data from order cost which are matching with the order table. So basicly I should see data from this year and the last 2 years. 

I see data from order table from the whole year but data from the cost table I see only from the rangefilter.

The rangefilter is only linked with the order table and not with the cost table. The cost table is only affected with the join option.

This is how it looks in my vizualization:

Does someone has similar issues or maybe know what could be the problem?

Thank you for help.

Kind regards

Marijan

[lbendlin]

Use your favorite XMLA tool (like SSMS or DAX Studio) to verify that Incremental Refresh is actually working.  You can see that by the number of partitions for that table.

NOTE:  Any time you make meta data changes upload the PBIX this will restart the Incremental Refresh partitioning.

[Marijan]

I will talk to my IT to get one of this tools.

But could it be that incremental refreshin dataset and join function in Power Query are not working well together?

My thougts:

My main table is the order table. In this table I have a range filter set for a time range of one month. This filter will be dynamicaliy changed trough the incremental refresh in the dataset when it gets published.

But before this, my data is loaded trough the Power Query into the dataset. My second table the cost table is linked with the ord table and I use a join typ to load only linked data which are matching with the order table. Now for the cost table it will also only load the data from one month because of the range filter in Power Query. Later the incremental refresh will dynamicaly change the range filters to one year, but this does not affect the other tables. Even not with the join option, becuase this filtering was already done in the Power Query befor the incremental refresh starts.

Do you think my thoughts on this behaviour is correct?

[lbendlin]

You will want to avoid any additional transforms on an Incremental Refresh data source, or do them AFTER you applied the partition filter. Let the data model do the joins for you.