Detail Rows in Power BI
Let’s say you have an Analysis Services 2017 (1400 CL) model and you’ve implemented several detail rows expressions to help users drill into the details of their reports. Let’s also assume that feedback has been positive but users (and maybe others in BI/IT) would prefer to analyze data in Power BI rather than Excel or to have the option to use both tools.
The Requirement
Allow users (Power BI Pro users) to retrieve the details (detail rows expressions) for at least one year of reseller sales data for any single reseller into Power BI.
The Solution
Create a Power BI template which passes parameter selections to a DAX query based on the DETAILROWS() function. It could look like a normal Power BI template per the following image:
The DAX query generated by the template would look like this:
Only a single table containing the 17 columns defined in the default detail rows expression for the Reseller Sales table is retrieved into the Power BI report. Therefore, the user doesn’t need to browse the fields list to search for the columns within the many tables of the model.
The Reseller Sales Row Count measure in the Analysis Services model does not have a detail rows expression defined for it and thus the DETAILROWS() function will retrieve the default detail rows expression for the Reseller Sales table. Choosing a generic measure which will not be deleted or modified and which would never have its own detail rows expression ensures that the default detail rows expression will be returned.
How to Build It
Essentially you only need the following two components:
- The parameters for the selected reseller and the start date of the query
- The start date needs to be relative to the current date such that the report can be scheduled for refresh.
- M queries returning list values provide the dropdown for users to select from.
- A DAX query which integrates the two parameters from Power BI and is passed back to the Analysis Services 2017 (1400 CL) model.
Per the Query Editor in Power BI Desktop, there are nine queries but only the Reseller Sales Details query is loaded:
Parameters
The two list queries – HistoryDays and ResellerNames, are used to provide the available values to the Days of History and Reseller parameters, respectively. HistoryDays is a simple {90,180,270,360} expression while ResellerNames is an independent DAX query against the tabular model to retrieve the distinct reseller names:
ResellerNames = AnalysisServices.Database(
AnalysisServicesServer, AnalysisServicesModel,
[Query="EVALUATE VALUES('Reseller'[Reseller Name])"])
The StartDate query is driven by both the user selection for Days of History and the CurrentDate query:
CurrentDate + #duration(-#"Days of History",0,0,0)
You might find my previous post regarding a last refreshed message in Power BI reports to be useful in understanding the CurrentDate query.
Note: Normally there would be parameters for the server and database but in this scenario with the template we only want to expose the two parameters to select. Queries containing the name of the server and model are used instead.
Reseller Sales Details
The Reseller Sales Details query requires some text manipulation to create the query string with both the Reseller and the StartDate parameters built in:
let ResellerSalesDetails = AnalysisServices.Database ( AnalysisServicesServer, AnalysisServicesModel, [Query= "EVALUATE CALCULATETABLE(DETAILROWS([Reseller Sales Row Count]), 'Reseller'[Reseller Name] = "& """" & Reseller & """" &", 'Date'[Date] >= DATEVALUE("& """" & Date.ToText(StartDate) & """" &") ) "] ), OpenBracket = Table.TransformColumnNames(ResellerSalesDetails, each Text.Replace(_,"[","")), ClosedBracket = Table.TransformColumnNames(OpenBracket, each Text.Replace(_,"]","")) in ClosedBracket
- Double quotes are needed to surround the text values passed into the filter arguments of the CALCULATETABLE function and these are added via concatenation.
- The final two expressions (OpenBracket, ClosedBracket) remove the open and closed bracket characters from the column headers.
Wrapping Up
Excluding the string manipulation for the filter parameters, the DAX query is short and simple because the Analysis Services model already has a detail rows expression which selects 17 columns related to reseller sales. Additionally, if the default detail rows expression is modified (such as adding a column), this will be picked up by the Power BI template.
Moreover, since the query is filtered to a specific reseller and trailing amount of history (max of 1 year), the query is light to execute and the result set is small. An interesting alternative (maybe a future blog post) could be to let the user retrieve up to the top X resellers into a single Power BI report.
Insight Quest Update
- I’m sorry I wasn’t able to get these posts out on Thursday – I target every Thursday.
- You can click ‘Follow’ to stay updated whenever a new post is published.
- I probably won’t be able to blog again until 1/25.
- We may look at leveraging the new DETAILROWS() function within Reporting Services reports (RDLs) at that time.
Insight Quest 
2 comments