Composite Data Model - unable to refresh with a calculated table?

[pbi-ai]

I've created a report that connects to 3 existing Power BI Datasets via Direct Query. These 3 datasets have a date column as a common denominator

So I created a calculated table called Dates to join against these 3 datasets so that I can filter on a Date and it'll apply to all datasets:

Dates = CALENDAR(EDATE(EOMONTH(TODAY(), -1 ), -12) + 1, TODAY())

However, doing this...I'm unable to set up a refresh in the Power BI Service. Is this a known limitation?

When I delete the calculated table:

when I include the calculated Date table:

Note: I don't need a Gateway because all of my sources are in the cloud. In this case I have 3 datasets from Power BI and 1 calculated table that's just generating dates for the last 12 months based on today's date. It is an import table, but not imported from a data source...it's generated.

[v-yueyunzh-msft · Accepted solution]

Hi , @pbi-ai 

Based on my previous testing and research experience, the reason why your "scheduled refresh" is not turned on should have nothing to do with your use of calculated tables, you can first try to check whether all the credentials configured for the data source under the "Datasource credential" section are valid:

You can try to re-edit the credentials for each data source and check if "scheduled refresh" can be enabled.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[pbi-ai]

@v-yueyunzh-msft - my data source credentials section has that exact same message as your screenshot. What credentials should I be using here? 

Also, I forgot to mention...while I'm unable to set up a scheduled refresh...I am able to do an on-demand refresh without modifying any credentials. So for now if I need to use this composite model report, I just click the refresh button to do an on-demand refresh so I have the latest data. So is this really a credential issue?

Also, note that if I remove the calculated table...it does show the option to refresh visuals on a schedule.

[v-yueyunzh-msft]

Hi , @pbi-ai 

I did the test, I used a hybrid data model: Direct Query linked sqlserver, and created a calculated table:

And after i configure the credential for my data source, i can configure the schedule refresh successfully.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[pbi-ai]

@v-yueyunzh-msft - yep, that did it! I actually had a little (x) next to "Edit Credential", which I noticed you didn't have. I updated the credential and then I was able to set up the refresh. Thank you very much for your help

[Sapagrino]

I am working right now on something similar, hoping to be where you got to here on my own datasets to combine.  How is your project coming?  Any successes or challenges since 2022-Oct to share?  I presume that when I get caught up to you, I'm going to want there to be an easy way to move custom visuals from individual files to the rolled-up dataset, etc.  Thank you for sharing.

[pbi-ai]

@Sapagrino - You can combine any number of datasets, add any custom/calculated tables you want. However, once you publish that composite model/dataset...in the Power BI Service go to the "Data source credentials" section and make sure all connections have a valid credential. In my case, it's a Microsoft account (we have a service account for Power BI). Hope that helps.

[Sapagrino]

Thank you for the tip.  I will look out for that.  Maybe you have a helpful thought on the following?  I have two major types of datasets (SetType1 and SetType2).  Unfortunately, because of the massive size of SetType1's population, it is spread across 5 separate files (i.e., 5 pbi desktop files for different time periods, each published to its own PBI service dataset and report).  I wish I could now simply append all 5 into one single table (since all columns are nearly the same) that I would then join to SetType2.  I'm just wondering how best to think about appending things and joining things as I compile it.  This isn't really my main skill set, but I'm learning how to do do a ton of things pretty quickly.  Thanks for any thoughts you have.

[pbi-ai]

@Sapagrino - if you have 5 different datasets and bring them into a Direct Query model together and then try to UNION them, it's really the same thing as never having 5 different datasets to begin with and having 1 single dataset with all of them combined from the start. Your best bet would be to have a singular dataset, less work and less complexity that way. But if you absolutely must keep them separate, you go to the Power Query Editor and use Append Queries.