How to create an Excel report using PowerBI as a data source in Mail & Deploy using DAX queries (with example)?

How to create an Excel report using PowerBI as a data source in Mail & Deploy using DAX queries (with example)?

FIRST CREATED ON 23 Oct 2025  I  AUTHOR Emma Camacho

Creating an Excel report using a PowerBI as a data source in Mail & Deploy allows you to leverage powerful data analysis capabilities. Here's an example to help you get started:

Use case
Design the 3 Top Stores Analysis Report.

Mail & Deploy Report









How to get there?

PowerBI Server Database datasource

How to configure a PowerBI Datasource in version 3.6 and higher?

This example uses the PowerBI report Sales & Returns Sample. You can find it here: Sales & Returns Sample v201912.pbix.

Datasource definition



Report Template




The template is attached to this article.

PARAMETERS

Datasource
"M&D Demo_Sales & Returns Sample v201912"
This is the name of the Datasource used in the M&D Datasource definition and it is used to ease the use of the function EvaluateDaxOrDmvDatasourceExpression.

ELEMENTS

NetSales
EvaluateDaxOrDmvDatasourceExpression( [ReportParameter].[Datasource], "EVALUATE {[Net Sales]}  " )

Units Sold
EvaluateDaxOrDmvDatasourceExpression( [ReportParameter].[Datasource], "EVALUATE {[Units Sold]} "   )

Category Breakdown
Source: DAX/DMV Query
Query Datasource: M&D Demo_Sales & Returns Sample v201912
Query: "EVALUATE ADDCOLUMNS( SUMMARIZE( Product, Product[Product] ), ""Net Sales"", [Net Sales], ""Units Sold"", [Units Sold] ) ORDER BY [Net Sales] DESC"
Insertion Mode:  Write To Worksheet
Row Insertion Mode: Overwrite

Store_rep

EvaluateDaxorDmvDatasourceExpression(
[ReportParameter].[Datasource], 
"EVALUATE "&
"topn(3, "&
"    SUMMARIZECOLUMNS( "&
"    Store[Store], "&
"   ""Net Sales per Store"" ,[Net Sales] , "&
" ""Units Sold per Store"",[Units Sold] "&
" ), "&
" [Net Sales], DESC "&
")" &
"ORDER BY [Net Sales] DESC ", 1 )

Store
[RepeatedValue].[Store].Value("Store[Store]")

Net Sales_perStore
[RepeatedValue].[Store].Value("[Net Sales per Store]")

Units Sold_perStore
[RepeatedValue].[Store].Value("[Units Sold per Store]")

CategoryBreakdown_perStore
Source: DAX/DMV Query
Query Datasource: M&D Demo_Sales & Returns Sample v201912
Query:   "EVALUATE " &
" CALCULATETABLE( " &
" SUMMARIZECOLUMNS( 'Product'[Product] , ""Net Sales"", [Net Sales] ) ,Store[Store]=""" &[RepeatedValue].[Store].Value("Store[Store]")& """) ORDER BY [Net Sales] DESC "
Insertion Mode:  Fill MSO Chart


Looking for Additional Guidance?


If you require more detailed information or further assistance, please visit our comprehensive Online Documentation. Our resources are designed to help you navigate all features and functionalities effectively.