Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Gabriela Ganciu]

Hello,

 

I am using a DIrect Query data source connection and trying to create a report in Power BI Desktop. Due to its limitations (lacking date hierarchy etc) I need to manually create a calendar table. I did a bit of research and notice this seems to be the solution people found.

However the New Column or New Table options in the Modelling tab are not available, only the New Measure one is and I am not sure that is fit for purpose here.

Can anyone advise? Why would these features not be available? Do I need to request access? Does it need to be done by the Developers via the API? Or is there maybe another solution?

Sorry for the load of questions, still new at this thing. Any suggestion would be much appreciated!

 

Thank you

——————————
Gabriela Ganciu
Data Analyst
——————————

[pouneh Samavatian]

I think you need to go under Home and the Transform data

Then you will se the New Column or New Table 🙂

——————————
pouneh Samavatian
——————————
——————————————-

[Gabriela Ganciu]

Hi

Thanks for replying!
When going under Transform Data? this is all I get:

Strangely, when selecting ‘Data source settings’ it’s asking me to select my data source once again and creates a new empty dashboard.

However if I try create a new report (without selecting a data source first) and go to Transform Data > Data Source Settings, I get this:

Could you please advise?

——————————
Gabriela Ganciu
Data Analyst
——————————
——————————————-

[pouneh Samavatian]

don’t click on the arrow but on the picture( Transform data) then you will get into this page with your own data:

——————————
pouneh Samavatian
——————————
——————————————-

[pouneh Samavatian]

on this pic but not the arrow

——————————
pouneh Samavatian
——————————
——————————————-

[Gabriela Ganciu]

Hi

It looks like ‘Transform data’ (which seems to take you to the Query Editor) ??is not an option for me. Can it be enabled by any chance? Or do you know why it would be grayed out?

Thanks,

——————————
Gabriela Ganciu
Data Analyst
——————————
——————————————-

[Ben Howard]

Hi Gabriela,

Can you let me know what version of Power BI desktop you are using.  What you are wanting to do is possible, but it would appear you have an old version of Power BI.  You need a feature called Composite Models which will allow both direct query (some exceptions here, see the article) and other queries and the creation of other tables.  For a DAX date table you could use the rather complex one from SQLBI or ?this one.  Once the date table is created then you can relate the two tables in the model.

——————————
Ben Howard, UK.

Please mark any answer as recommended if it helps you.
——————————
——————————————-

[Gabriela Ganciu]

Hi

Thanks so much for your reply. I will definitely look into this.?
Power BI seems to be up to date though, the version I’m using is 2.86.902.0 64-bit (October 2020). Could this be the problem?
I Have been advised by the support team that since we’re using a Direct Query connection, creating a new table is not possible. Is this right?

Thank you,

——————————
Gabriela Ganciu
Data Analyst
——————————
——————————————-

[Ben Howard]

Hi , the version is fine.  What is the data source of the DQ??  The following limitations apply depending on the source.

Limitations and considerations

This release of composite models presents a few limitations:

Currently, incremental refresh is supported for composite models connecting to SQL, Oracle, and Teradata data sources only.

The following Live Connect multi-dimensional sources can’t be used with composite models:

• SAP HANA
• SAP Business Warehouse
• SQL Server Analysis Services
• Power BI datasets
• Azure Analysis Services

When you connect to these multi-dimensional sources by using DirectQuery, you can’t connect to another DirectQuery source or combine it with import data.

The existing limitations of DirectQuery still apply when you use composite models. Many of these limitations are now per table, depending upon the storage mode of the table. For example, a calculated column on an import table can refer to other tables, but a calculated column on a DirectQuery table can still refer only to columns on the same table. Other limitations apply to the model as a whole, if any of the tables within the model are DirectQuery. For example, the QuickInsights and Q&A features aren’t available on a model if any of the tables within it has a storage mode of DirectQuery.

——————————
Ben Howard, UK.

Please mark any answer as recommended if it helps you.
——————————
——————————————-

[Gabriela Ganciu]

Hi

I was using a Power BI dataset as a source, so that is the reason why those features are not available. ? I will ask the engineers who build this connection if it is possible for me to access the source in another way.

However, to give you a bit of context as to how I got here, what I am trying to achieve by manually creating a date hierarchy is to have a nice looking graph that compiles my order data instead of showing it one-by-one.   Please see below:

Current

Desired result

Is there another solution that doesn’t involve date hierarchy?

Thank you very much for taking the time.

Regards,

——————————
Gabriela Ganciu
Data Analyst
——————————
——————————————-

[Ben Howard]

Hi , I cannot see the images.  Are you able to share them another way??

——————————
Ben Howard, UK.

Please mark any answer as recommended if it helps you.
——————————
——————————————-

[Gabriela Ganciu]

Hi

Please see below links.
Current: https://drive.google.com/file/d/1qHmjk4b2xm-Blo-WDGxjFuyF1OQkRlt1/view?usp=sharing??
Desired result: https://drive.google.com/file/d/1a3B5n5AavpPoFMjGEVm2L32JNj4zDzN4/view?usp=sharing

Thank you,

——————————
Gabriela Ganciu
Data Analyst
——————————
——————————————-