Power BI

[AjitSingh100488]

Hi Team,
These are the following questions related to Power BI-

1. Are Mdx sql operations supported in direct query mode? Right click on any measure -> show details gives error in Excel.

2. Can we create custom fields in the excel connector? Explain the process.

3. Is detail row expressions for drill through (including the related table in the model) for direct query model available? If yes, how can we implement it?

4. How can we enable "Additional actions" on right click in Excel? What are the all "Additional actions" availabe?

5. Is there any Analyze in Excel standard template?

6. Is there any centralized approach for Analyze in Excel reports for sharing within users, like PowerBI service?

7. Is "Excel for Web" different from "Excel Desktop" connector for PowerBI? "Defer Layout" does not seem to be appearing in "Excel for Web".

[rajendraongole1 · Accepted solution]

Hi @AjitSingh100488  - Please find the following answers for each question:

1. Are MDX SQL operations supported in Direct Query mode?

A. In Direct Query mode, MDX operations can be limited, especially when interacting with Excel's "Analyze in Excel" feature.

2. Can we create custom fields in the Excel connector?

A. The Excel connector in Power BI allows access to data but does not natively support creating custom fields directly in the connector. However, custom fields can be added in Excel or within Power BI using measures, calculated columns, or custom transformations.

3.  Is detail row expressions for drill-through (including related tables in the model) for Direct Query model available?

A.Yes, detail row expressions are available for Direct Query models, but with limitations based on the dataset design.

4.How can we enable "Additional actions" on right-click in Excel?

A. Additional actions (e.g., Drill-through, Expand, Collapse) are tied to the functionality of the data source and cube.

5.  Is there any Analyze in Excel standard template?

A. Power BI does not provide a built-in template for Analyze in Excel. However, you can create and save your own template with pre-defined pivot table configurations.

6. Is there any centralized approach for Analyze in Excel reports for sharing within users?

A. Power BI does not have a centralized repository for Analyze in Excel reports. Link the Analyze in Excel reports within Power BI Service using URLs or shared workspaces.

7. Is "Excel for Web" different from "Excel Desktop" connector for Power BI?

A. Yes, there are differences between Excel for Web and Excel Desktop when connected to Power BI datasets

I hope the above answers helps for better understanding. please find the links FYR:

mdx construct that is not allowed on DirectQuery M... - Microsoft Fabric Community

Analyze in Excel: Only Measures as Values? - Microsoft Fabric Community

Power BI DirectQuery best practices

Power BI Connection Types: DirectQuery, Live, or Import? Tough Decision! - RADACAD

[Anonymous]

Hi @AjitSingh100488 

Thank you rajendraongole1 for your prompt and wonderful reply.

If rajendraongole1's reply helped you solve your problem, please consider accepting his reply as a solution, which will facilitate other users with the same problem to find a solution more easily.
If you still have other questions, please feel free to contact me.

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

[AjitSingh100488]

Hi @Anonymous ,
Greetings!! 
I have following questions related to PBI-
1. What is query folding and how can we make best use of it to improve performance? 
2. How to enable query folding in power query for a table?
3. How to modify Max Intermediate Row Set Count for PowerBI?
4. What are the pre-requisities?

 
please help me with these questions.  
Thanks,

Ajit 

[Anonymous · Accepted solution]

Hi @AjitSingh100488 

Please find the answers to each question below:

1. What is query folding and how can we make best use of it to improve performance?

A. Query Folding is an optimization mechanism in Power Query that pushes as much of the computation work as possible to  the data source, instead of executing it in Power BI itself. By delegating operations like filtering, sorting, and aggreation to the database, query folding can significantly improve performance, especially when dealing with large datasets. Using query folding helps reduce memory usage and processing time.

Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn

Query Folding in PowerBI - Microsoft Fabric Community

2. How to enable query folding in power query for a table?

A. Power Query automatically enables query folding for certain data sources, such as SQL Server, Azure SQL Database, and others. In the Power Query editor, select a step and right-click. If query folding is supported for that step, you will see an option to "Native Query". If this option isn't available, it means query folding isn't supported.

Query folding guidance in Power BI Desktop - Power BI | Microsoft Learn

3. How to modify Max Intermediate Row Set Count for PowerBI?

A. To modify the maximum intermediate row set count for Power BI, you can adjust it in the Power BI Premium capacity settings.

https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-premium-workloads#max-intermedia... 

4. What are the pre-requisities?

A. 

Data source support for query requests:

The data source must be able to accept and process query requests. The query folding mechanism is primarily available in connectors for structured data sources such as, but not limited to, Microsoft SQL Server and OData Feed.

Avoid starting a query with native SQL code:

Make sure that the query steps are pushed to the data source for execution as early as possible, so you need to avoid too many native operations (e.g. sorting, filtering, etc.). If a query starts with native SQL code, it will not be possible to implement query folding in subsequent steps.

By the way, to ask a new question please post a new thread if possible so you can get a quicker response.

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

[AjitSingh100488]

Hi @Anonymous 
Thank you very much for the quick response on my questions, and yes, I'll post a new thread for my next/new questions.

Thanks,

Ajit 

[rajendraongole1 · Accepted solution]

Hi @AjitSingh100488  - Please find the following answers for each question:

1. Are MDX SQL operations supported in Direct Query mode?

A. In Direct Query mode, MDX operations can be limited, especially when interacting with Excel's "Analyze in Excel" feature.

2. Can we create custom fields in the Excel connector?

A. The Excel connector in Power BI allows access to data but does not natively support creating custom fields directly in the connector. However, custom fields can be added in Excel or within Power BI using measures, calculated columns, or custom transformations.

3.  Is detail row expressions for drill-through (including related tables in the model) for Direct Query model available?

A.Yes, detail row expressions are available for Direct Query models, but with limitations based on the dataset design.

4.How can we enable "Additional actions" on right-click in Excel?

A. Additional actions (e.g., Drill-through, Expand, Collapse) are tied to the functionality of the data source and cube.

5.  Is there any Analyze in Excel standard template?

A. Power BI does not provide a built-in template for Analyze in Excel. However, you can create and save your own template with pre-defined pivot table configurations.

6. Is there any centralized approach for Analyze in Excel reports for sharing within users?

A. Power BI does not have a centralized repository for Analyze in Excel reports. Link the Analyze in Excel reports within Power BI Service using URLs or shared workspaces.

7. Is "Excel for Web" different from "Excel Desktop" connector for Power BI?

A. Yes, there are differences between Excel for Web and Excel Desktop when connected to Power BI datasets

I hope the above answers helps for better understanding. please find the links FYR:

mdx construct that is not allowed on DirectQuery M... - Microsoft Fabric Community

Analyze in Excel: Only Measures as Values? - Microsoft Fabric Community

Power BI DirectQuery best practices

Power BI Connection Types: DirectQuery, Live, or Import? Tough Decision! - RADACAD