Forum Discussion
DAX: Generic Measure Formula to Display Value on Top Level Row Element Only in Matrix Visual
I am working on a report from a Power BI dataset that has a fact table containing invoice line data and dimensions containing data about dates and customers. I am using a matrix visual because the business wants to be able to expand/collapse rows and that seemed like the easiest route.
Relevant Info:
factInvoiceSales: invoiceNumber, invoiceLine, invoiceQty, invoiceAmt, customerKey, invoiceDateKey
dimCustomer: customerKey, accountNumber
dimCalendar: dateKey, calendarYear, calendarMonth
dimCustomer.customerKey 1:* factInvoiceSales.customerKey
dimCalendar.dateKey 1:* factInvoiceSales.invoiceDateKey
Matrix Rows: accountNumber > calendarYear > calendar Month
Matrix Columns: N/A
Matrix Values: invoiceQty > invoiceAmt > lifetimeSales
The lifetimeSales measure is the one I have a question about as the business has the requirement that the lifetimeSales only display on the row that has the accountNumber and not repeat itself in the rows containing the year and month breakdowns of the sales within an accountNumber. The lifetimeSales should ignore all filters placed on elements in the report except those on the accountNumber and on the factSales table. I have come up with a measure that works, however, it's highly dependent on the data in the report and any change to the structure of the rows would blow it out of the water. The measure as I have it now is:
lifetimeSales = IF(ISINSCOPE(dimCalendar[calendarYear]), BLANK(), CALCULATE(SUM(factInvoiceSales[invoiceAmt]), ALLEXCEPT(factInvoiceSales, dimCustomer[accountNumber])))
This works great as any row that is at or below the calendarYear level in the report DOES show a blank for lifetimeSales while the only row above that level (accountNumber) performs the calculation and displays the lifetimeSales for the customer. It also ignores all filters except those on accountNumber and invoiceNumber (I didn't test anything further in factInvoiceSales). However, I would like to know if it's possible to make this more generic by detecting what the top level element is in the matrix rows and displaying the calculation only in that top level row. I'm a DAX novice, but in looking through the documentation of the DAX functions, I didn't see anything that stuck out that I could use to accomplish this. I was envisioning a function that could tell me what the depth of the row is and if it's not the minimum depth, then make it blank. That way, if the business decides that the row elements need to be re-arranged or if this measure is required in another report, it doesn't have to be rewritten (unless the actual calculation on invoiceAmt changes).
I'm honestly expecting that this is not possible, but I'm hoping there's a DAX expert out there that knows of a way to accomplish this. Please let me know if any more detail is required. Thanks in advance for your help.
2 Replies
TomM_5 , Based on what I got, check if this can help
lifetimeSales =
VAR isTopLevel =
NOT ISFILTERED(dimCustomer[customerKey]) &&
NOT ISFILTERED(dimCalendar[dateKey]) &&
(HASONEVALUE(dimCustomer[accountNumber]) || ISFILTERED(dimCustomer[accountNumber]))
RETURN
IF(
isTopLevel,
CALCULATE(
SUM(factInvoiceSales[invoiceAmt]),
ALLEXCEPT(
factInvoiceSales,
dimCustomer[accountNumber]
)
),
BLANK()
)amitchandak This formula didn't appear to work for me. When I created a measure with this formula, it calculated the lifetime sales correctly, however, expanding from accountNumber to calendarYear repeated the lifetime sales for each calendarYear row. This formula does appear to remove the result from the grand total row though.
My original measure formula functions correctly in my report, however, I was hoping there was a way to do something more generic to simply detect the top level row that didn't rely on the data itself. So if the business decided that some other column such as country or state/province needed to be inserted above accountNumber, it wouldn't require a different measure or a rewrite of the existing one.
I was hoping to be able to do something like IF(ROWDEPTH() = 1, CALCULATE(SUM(factInvoiceSales[invoiceAmt]),ALLEXCEPT(factInvoiceSales,dimCustomer[accountNumber])),BLANK()) where ROWDEPTH would be a function (built-in or otherwise) that determines at what level of the table the measure calculation is occuring.....kind of like returning the depth of a node in a binary tree in Java or C++. I looked at the PATH functions thinking maybe something like PATHLENGTH would tell me if I was at the top level, but I don't understand them well enough from the documentation to know whether anything like that would actually work.
