cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Problem with Dimension and Datamodel

Hello,

I've been trying to get a sensible data model and calculations for weeks. I've been working with PQ for 2 years, but I can't get it right. The following scenario: I have several dimensions that build on one another hierarchically. This creates so many combinations that these test lines alone with 11 lines become 71 lines.

In the real data, however, there are an incredible number of combinations in Cat C/D/E, so that this ultimately results in a few GB in the data model.
So I have to manage to keep the data as small as possible and still be able to link the categories with each other, since pivot tables with slicers are ultimately required. Ultimately, I want to measure what percentage of the categories fall into which other categories.

I've tried synthetic keys and many others - I can't do anything because of the 1:N relationship in Excel Power Query. I really need help here.

Thanks for the help.
Rico

2 REPLIES 2
Super User

CatB (Sex) is independent, you can normalize that out.  For the other categories can you guarantee that each element only appears once in the hierarchy tree? Or are there duplicates?

New Member

Hello,

Thanks for the answer 🙂 Well, category A can have different items from category C (see line 3 and 4 in the screenshot). These, in turn, are different items from category D and E. Basically, the individual items result from E + D + C = A. However, there should be no duplicates per line (line 8). These will then be deleted. And yes, actually it's like a family tree where category a is the root and then it branches further down. It is then counted how many items from the lower nodes lead to which node above it.

So I despair because the amount of combinations then becomes 1000 (A) x 1-2 lines (C) x 1-10 lines (D) x 1-10 lines (E) with about 1000 lines. Quite apart from the fact that I can't connect the dimensions because there is no 1:n connection. So: Questions over questions.....

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors