Table manipulations using DAX
1. Table manipulations using DAX
Welcome back! In this demo we’ll have a deeper look at the marketing costs, and we’ll use the powerful SUMMARIZE() function to do so! The Dim_Account table has several accounts, such as IT Cost, Property Cost, and Marketing Cost. These are hierarchically structured, and all marketing costs are collected under Account Level 5. In this case, I’m only interested in accounts with a marketing cost, combined with the AccountName, and the AccountKey, to create a relationship with the fact table. So let’s create a new table, called Dim_MarketingCosts. In a SUMMARIZE() function, I specify the table to start from, Dim_Account, and group by the columns AccountName, AccountKey, and Account Level 5. For demonstrative purposes, I’d like to create a new column in this table, called “Is Marketing Cost”. To do this, I wrap the SUMMARIZE() call inside an ADDCOLUMNS() function, with the name of the new column, and an IF() statement, returning True when Account Level 5 equals Marketing Cost. There we have it! An overview with all account names and whether they are marketing costs or not. Note that you could have obtained the same result with a FILTER() function, but it's good to know alternatives exist. Let’s navigate to the model view to investigate the relationship between the fact table and the new Dim_MarketingCosts table. There is no relationship yet, so let’s establish one using a one-to-many relationship on AccountKey. Let’s now use our new table to visualize the transaction values for marketing costs, using a ribbon chart. Use the date from the Dim_Calendar table as the Axis, the AccountName from the new table as the Legend, and the Actual Transaction Amount as the Values. Finally, l’ll add the Is Marketing Cost field as a filter, only selecting the True marketing costs. The chart reveals marketing costs are going down each year, and that print is the account with the highest marketing costs for 3 consecutive years already. That’s it for this demo. It’s time for you to start summarizing in the exercises!2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.