Suggestions for looking up dimensions at different levels using DAX...

[Matty]

Hi Team,

Looking for a bit of advice as to how I could do the following in the most efficient way...

Fact table as follows:

Hierarchy Level 1	Hierarchy Level 2	Hierarchy Level 3	Hierarchy Level 4	Sales
#	B1	#	#	10​
#	#	C1	#	5​
#	#	#	D1	20​
#	B2	#	#	5​
A1	#	#	#	15​
#	#	C4	#	25​
#	#	#	D2	10​

Dimension table as follows:

Hierarchy Level 1	HL1 Description	Hierarchy Level 2	HL2 Description	Hierarchy Level 3	HL3 Description	Hierarchy Level 4	HL4 Description
A1	US	B1	North	C1	North East	D1	Shop 1
A1	US	B1	North	C1	North East	D2	Shop 2
A1	US	B1	North	C2	North West	D3	Shop 3
A1	US	B2	South	C3	South East	D4	Shop 4
A1	US	B2	South	C3	South East	D5	Shop 5
A1	US	B2	South	C3	South East	D6	Shop 6
A1	US	B2	South	C4	South West	D7	Shop 7

There is a relationship between the fact table and dimension table on the field 'Hierarchy Level 4', which is the lowest (most granular) level in the dimension table. The problem I have is that my fact table does not necessarily have Hierarchy Level 4 populated for each record, meaning I can't use this relationship for looking up anything in this scenario.

Here's what I want things to look like (note that descriptions are populated even in the absence of a Hierarchy Level 4):

HL1 Desc.	HL2 Desc.	HL3 Desc.	HL4 Desc.	Sales
US	North	#	#	10​
US	North	North East	#	5​
US	North	North East	Shop 1	20​
US	South	#	#	5​
US	#	#	#	15​
US	South	South West	#	25​
US	South	South West	Shop 7	10​

Just wondering how others would tackle this problem?

Cheers,

Matty

 

[Matty]

Hi,

Just to follow up on the above.

I have checked the real data again to ensure the above held in all situations. Actually, things *aren't* quite as complicated as I'd painted above!

The fact table is actually as follows:

Hierarchy Level 1	Hierarchy Level 2	Hierarchy Level 3	Hierarchy Level 4	Sales
A1	B1	#	#	10​
A1	B1	C1	#	5​
A1	B1	C1	D1	20​
A1	B2	#	#	5​
A1	#	#	#	15​
A1	B2	C4	#	25​
A1	B1	C1	D2	10​

So, my join (relationship) to the dimension table (via Hierarchy Level 4) works fine if the fact table contains data at this granularity, but if it doesn't, the relationship does not work. I can create additional dimension tables for each of the higher hierarchy levels (where Hierarchy Level 4 is missing), but this doesn't seem like the best solution as it means users have to go to different dimension tables to get the dimension they need.

The ideal situation would be one dimension table that is able to join to the fact table at different levels depending on the level the given record is maintained at, but I can't think of a way to get this to work.

Any suggestions most welcome.

Cheers,

Matty