Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
kostaszogo21
Frequent Visitor

Two sets of header-details Tables. A complete nightmare!

Hello guys!
I'm in a really bad situation and i would appreciate all the help i can get from power bi ninjas/gurus out there.

I recently replaced a colleague who resigned, in a project that seems to be above my skill level (i've been working on powerbi for less than a year) and what i "inherited" is a model that i've never seen before.
Think of a Telco's call center where the customers enters the first system of the call center (automated messages) and if he's issue isn't solved then he is re-directed to the second system where he gets to talk with an actual person/agent.

The model i inherited is like that:

kostaszogo21_0-1676621052088.png

Table A is for the automated system of the call system and table B for the agent system.
But in each of these systems, on the first "section" general details about the call is gathered (call_id, phone_number, date etc) and on the second "section" more info about the specific steps the client is entering is available (ex the client talks with one agent and the is sent to a second one and so on). That's why i'm having two sets of header-details table.
Size of tables:
-Table A header: 10M rows
-Table A details: 120M rows
-Table B header: 11M rows
-Table B header: 30M rows

The data source of the pbix is a tabular model in Analysis Services (Visual Studio).
The problem is that all these tables have dimensions the users want to use as filters. So the user must be able to  filter a measure created in Table A header with a dimension in Table A details, or even with a dimension in Table B details.
So when i have a measure that looks like the following:
Measure1=
           CALCULATE(
                             COUNT (Header A[dimension1]),
                              filter(Details A,  Details A[dimension2])
                             )

and my entire page is filtered, let's say with a dimension from Header A, the graph takes too long to load (sometimes around 30secs) and the CPU goes near 100%, which i know is really, really bad. I also know that the JOIN that is requiered for this type of Measure, is killing my performance.
As i am 5 days before  a UAT Test, i would like to know if there is a way/technique to fix this problem or at least improve the performance without changing the Data Model i've shown you!!!
I know that a star schema is the best solution but frankly i don't think there is enough time for that.
Any idea/proposal is more than welcome (the community is my only hope)

Thanks in advance guys 
PS: Unfortunately, no pbix file available because of the company's policy.
          
Kostas

1 REPLY 1
lbendlin
Super User
Super User

Sorry to hear about your predicament.  Please understand that without more context and without access to sample data (which does not need to contain any PII !)  the community won't be able to help you.

 

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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