Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

Table structure for making a dynamic and efficient scatter chart visual using a very large dataset

Hi Everyone,

 

I am having troubles building a report which requires to have a scatter plot with dynamic x and y axis with the calculation based on what the user selects.

 

The requirement is as follows:

1. User should be able to see only relevant KPIs based on selected group/s
2. User should be able to filter KPIs and/or components based on selections
3. User should be able to change scatter chart's x and y axes based on KPIs selected for each for analysis purposes.
4. The report should still be able to function efficiently (already have 2.5M+ rows already)

 

Any help would be highly appreciated.

2 REPLIES 2
TomMartens
Super User
Super User

Hey @Anonymous ,

 

your question is quite generic, and you might consider to provide a pbix that contains sample data and reflects your data model, upload the pbix and xlsx (if you used xlsx to create the sample data) to onedrive and dropbox and share the link.

 

Nevertheless, create slicers that allow for selections what should be shown on the x-axis and y-axis. The content for this slicers is derived from tables that are in no way related to your existing tables.

Assuming that one of theses tables is called "xaxis" and has a column called KPI, you can create a measure like so:

KPI for xaxis =
var selectedkpiforxaxis = SELECTEDVALUE('xaxis'[KPI] , <[name of th default measure]>)
return
SWITCH
    selectedkpiforxaxis
    ,"name of 1 kpi" , <[name of the corresponding measure]>
    ,"name of 2 kpi" , <[name of the corresponding measure]>
    , ...
    ,"name of n  kpi" , <[name of the corresponding measure]>
)

Create a 2nd measure for the content of the yaxis.

Use both of these measure on the scatter plot.

 

Hopefully this provides you with some new ideas.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Appreciate the response @TomMartens.

 

Based on what you've said, at the very least, I am going the right direction though the concept only works fine on a small dataset. The challenge comes in when it needs to calculate over 2M+ rows worth of dataset including the number of criteria it needs to work on (scatter chart loads forever).

 

Anyhow, do you have any tips on how I should modify my dataset structure? Basically, the fields are similar to - ID/Date/Criteria/SubCriteria/Value and is related to a roster table by ID.

 

My apologies as I am unable to upload anything as a sample - I am doing this using my company's network.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.