Need help modelling

[Tontaube2]

Hi,

I have searched the other answers, but I'm still stuck/ don't see the big picture.

I know HOW to pivot, merge, append - but don't know WHEN/IF.

I have answers to survey questions from companies as well as answers from reports published by the companies itself - each once a year.

The answers have attributes (e.g. hierarchy-level) as well as the companies (size, type, reportA: xy, reportB: xy).

The companies will be sliced by name, singular-filters (type A or B) and multiple-filters ("is family-friendly" yes or no AND maybe (checkbox) "is great" yes or no)

Here's a Lucidchart of this picture: https://lucid.app/lucidchart/71e6ae51-a9bf-43dc-899c-e6f79be9a1a2/view?page=0_0&invitationId=inv_2d0...

I struggle with which table to pivot - and thus create long, narrow tables, with multiple company-keys - and which to leave or make short & broad, with a single company-key.

Do I need one fact table or two?

Do I create one dim table for each of the slicers, or one for each type of slicer: single-value-slicers (On/Off, Yes/no) and one for multi-value-slicers ("Family-friendly yes/no" and maybe! (Checkbox) "Great" yes/no")?

Size is about 40 companies, 350 questions.

Could you advise me how to model it "better"/which tables to make long&narrow and which to make short&broad

[V-lianl-msft]

Hi @Tontaube2 ,

For performance reasons it is better that you reduce BD relationships.It is recommend that you activate bi-directional filtering in a measure definition by using the CROSSFILTER DAX function instead. Maybe you can consider using the bridge table as a slicer.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tontaube2]

Edit: here's a new picture: 

sd = single direction, bd = multi direction

I realized the Slicers would be a problem.

In which table do I have to pack the attributes for a company-slicer?

Hi,

I have searched the other answers, but I'm still stuck/ don't see the big picture.

I know HOW to pivot, merge, append - but don't know WHEN/IF.

I have answers to survey questions from companies as well as answers from reports published by the companies itself - each once a year.

The answers have attributes (e.g. hierarchy-level) as well as the companies (size, type, reportA: xy, reportB: xy).

The companies will be sliced by name, singular-filters (type A or B) and multiple-filters ("is family-friendly" yes or no AND maybe (checkbox) "is great" yes or no)

Here's a Lucidchart of this picture: https://lucid.app/lucidchart/71e6ae51-a9bf-43dc-899c-e6f79be9a1a2/view?page=0_0&invitationId=inv_2d0...

I struggle with which table to pivot - and thus create long, narrow tables, with multiple company-keys - and which to leave or make short & broad, with a single company-key.

Do I need one fact table or two?

Do I create one dim table for each of the slicers, or one for each type of slicer: single-value-slicers (On/Off, Yes/no) and one for multi-value-slicers ("Family-friendly yes/no" and maybe! (Checkbox) "Great" yes/no")?

Size is about 40 companies, 350 questions.

Could you advise me how to model it "better"/which tables to make long&narrow and which to make short&broad

---