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

[Anonymous]

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.

[TomMartens]

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

[Anonymous]

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.