Power BI: Hybrid dimensions - why you need them

This is a long article - important links are right at the end after the DAX section. A hybrid dimension is going to be really useful in a Power BI report. You can build the dimension in either Power BI Desktop or SSAS Tabular - but better to copy-paste after writing the DAX in DAX Studio. It may look like a standard junk dimension but there are three important differences. One, there is no need to crossjoin all the members. Two, the attributes and members are not junk, they are real attributes and members from your existing dimensions. Three, there is no need for a primary key to link to the fact table!

All of the DAX to create a hybrid dimension is in the pbix - link below. It involves the DAX UNION() function on the selected columns from your existing dimensions. You can then relate this dimension to your fact table with the DAX function TREATAS() - which creates a temporary, virtual relationship. Here is some pseudo code (lots examples in the pbix, and there is a sample later in this article):

CALCULATE Sales IF HybridDimension[Member] = "Country" THEN TREAT IT AS DimGeography[Country]

where DimGeography is an existing dimension with a permanent, physical relationship to the fact table. All the examples are using Adventure Works DW, so you can emulate if you want. Next are a few good reasons for you to look at hybrid dimensions (where the article number refers to one of my recent series of five articles on TREATAS() - all links later).

Some reasons for hybrid dimensions

1 so the user can switch the dimension/attribute shown in a table or on a chart axis - instead of many tables/charts you just need the one! For example, they might switch from dates to products, or from product size to product colour . Article 1

2 so the use can switch the dimension hierarchy level shown in a table or on a chart axis. For example, they might switch from country to city, or from year to quarter - and with no drilldown required, so it works in tables too. Article 1

3 to show multiple dimensions at once in a table or chart axis - without needing an awkward drilldown. Article 2

4 to indent child rows in a table from the parent. Articles 4/5

5 to show parents and children from the same dimension in a table or on the same axis of a chart - without needing an awkward drilldown. Articles 3/5

6 to show KPIs only on subtotals in a table, rather then the default of all the rows. Article 4

7 to introduce gaps between groups in a table or on a chart axis. Article 5

Each article has its own pbix link.

Here are a few types of hybrid dimension (all are in the pbix for this article):

Type A - as in the screenshot, single attributes from other dimensions

Type B - multiple attributes from other dimensions

Type C - Type A with gaps to delineate the source dimensions

Type D - Type B with gaps to delineate the source dimensions

Type E - Type C with superfluous leading/trailing gap removed

Type F - Type D with superfluous leading/trailing gap removed

The examples in the pbix show how to get the sorting column correct - instead of Accessories, Australia, Bikes, Canada we need Accessories, Bikes, Australia, Canada

Here's the DAX for Type A (see screenshot) - a DAX table and two measures:

TYPE A = ADDCOLUMNS(UNION(UNION(SELECTCOLUMNS(DISTINCT(DimGeography[EnglishCountryRegionName]), "Dimension", "Geography", "Attribute", "Country", "Member",

DimGeography[EnglishCountryRegionName]), SELECTCOLUMNS(DISTINCT(DimDate[CalendarYear]), "Dimension", "Date", "Attribute", "Year", "Member",

DimDate[CalendarYear])), SELECTCOLUMNS(DISTINCT(DimProductCategory[EnglishProductCategoryName]), "Dimension", "Product", "Attribute", "Category", "Member",

DimProductCategory[EnglishProductCategoryName])), "MemberSortColumn", [Attribute] & [Member])

Sales = SUM(FactInternetSales[SalesAmount])

Revenue A =

SWITCH(

   SELECTEDVALUE('TYPE A'[Attribute]),

       "Category", CALCULATE([Sales], TREATAS(VALUES('TYPE A'[Member]), DimProductCategory[EnglishProductCategoryName])),

       "Country", CALCULATE([Sales], TREATAS(VALUES('TYPE A'[Member]), DimGeography[EnglishCountryRegionName])),

       "Year", CALCULATE([Sales], TREATAS(VALUES('TYPE A'[Member]), DimDate[CalendarYear]))

   )

pbix file download - has all the DAX you need to get started - six pages of cool examples

https://1drv.ms/u/s!Arc3CaYbIk6Pk3oGhs5NwSGnRerO

live, interactive version

https://app.powerbi.com/view?r=eyJrIjoiMDI3NWUwZDEtZjc3ZS00N2NjLWExYmMtZTQ4NzQ5ZDYyZDgyIiwidCI6IjQwYmViMWU2LWExZTMtNDFhMy1iYmMxLTg1YWE2OGZmNTIwOCIsImMiOjh9

Article 1

Article 2

Article 3

Article 4

Article 5