A line-level table is normally the most granular data in a Data model. For example, consider an Order Line
table. The orders for each customer are stored one row per product line, and we have corresponding costs for each product on each line. When we generate a Table report for such data, we will have a separate line for each product which in itself is not wrong. But recently a customer asked me to initiate an export for the Table report in such a way that all the products for a particular order are contained in a single cell and the sales column should show the aggregated figure for all the products under OrderID
. To tackle this requirement, I created a calculated dimension using the Concat
function. The process is explained in the following recipe.
INLINE
table that contains the Order Line
table details:Orders: LOAD * INLINE [ Customer,OrderID,Product,Cost 1,201,Chain,20 1,201,Seat,40 1,201,Mudguard,50 2,202,Gloves,15 2,202,Basket,60 3,203,Helmet,70 ];
Sales by Order
.Products
:=AGGR(Concat(DISTINCT Product,','),OrderID)
Total Sales
:Sum(Cost)
OrderID
value are stringed together in a single cell and the sales figures are the aggregated figures for each OrderID
value.The Concat()
function gives us the aggregated string concatenation of all the product values separated by the ,
delimiter. The Concat()
function is an aggregation function and hence needs to be used with AGGR
in order to be used as a dimension. For the sake of our dimension, the products are grouped by the OrderID
.
The same functionality could have been achieved by defining products within a calculation in a measure as follows:
Concat(DISTINCT Product,',')
But by doing so, we won't be able to select the products for a particular OrderID
value inside the table.
When we use the calculated dimension, we get the advantage of selecting all the products for the OrderID
value in a single go by selecting a cell in the products column.