cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tontaube2
Helper III
Helper III

Structure Excel and Model

Hi,

 

"Power Query" might be the ideal subforum for this question.

 

I do have a Data source from Excel with 2 tabs for some example companies. One tab Answers, another tab with CompanyInfo

In PowerBI I structured it like this:

 

1: Company Info. To connect the other tables via singular Name & ID. At the moment only 5 example-companies exist, but 20 companies will follow later...

Tontaube2_0-1632387861582.jpeg

 -> my factTable

 

2: Company Filters. (Nearly) all Filter-values for the companies. Some Toggle-Filters (F1,F2), some single-options (F3,F4), some multi-options (F5, F6)

Tontaube2_1-1632387862207.jpeg

-> a dimTable to slice by

 

3: Answers (Values) AND two Filters which "belong" to these values: Segment, SubSegment

Tontaube2_2-1632387861757.jpeg

 -> in between fact and dimTable. Because the values are "connected" to a Segment/ SubSegment I cannot (or at least: should not) distribute them to other tables

 

Model: To show values from FactsAndFilters filtered by Slicers from CompanyFiltersSingle changes I have to make the connection between my 3 tables bidirectional. But no conflict/circumnavigation takes place.

Tontaube2_3-1632387861600.jpeg

 

But: performance sucks. Is this due to this (bi-directional), or due to the number of slicers?

 

Tontaube2_4-1632387861755.jpeg

 

 

Could someone please show me a "better way" to structure A) the Excel and B) the Model?

 

https://www.dropbox.com/s/4stlfmky53m8z9x/Example8.pbix?dl=0

 

https://www.dropbox.com/scl/fi/3t4vhputoqcl2e7b4o8pz/Example8.xlsx?dl=0&rlkey=hiynv57oht647y0wnc4bys...

1 ACCEPTED SOLUTION
Ehren
Microsoft
Microsoft

This seems like a DAX/modeling question rather than a PQ one.

Is the slowness in loading to the model, or interacting with the report? If it's the latter, consider moving the question to the area for DAX/modeling questions.

View solution in original post

2 REPLIES 2
Ehren
Microsoft
Microsoft

This seems like a DAX/modeling question rather than a PQ one.

Is the slowness in loading to the model, or interacting with the report? If it's the latter, consider moving the question to the area for DAX/modeling questions.

View solution in original post

It`s in interacting with the report. Thank you! I'll post it there! 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors