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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
scrawfor
Frequent Visitor

User defined scatterplot axes for regression

Long time viewer, first time poster. 

I have a table of facts. There are ~60 columns with ~30,000 rows. The table tracks process parameters. Each row in the table represents a single event. 

I would like to be able to have the report consumer select which column is used on the y-axis and the which is used on the x-axis.

 

There are about 3 columns that act as identifiers (event ID key, facility, date). The remainder are the data that is collected about the event. 

The goal is a user-defined linear regression between any two non-identifier columns that exist in the table.

 

Ive tried using field parameters, unpivoting the table and cross joining to get all possible combinations (this was unworkable), using a copy of the fact table to get the x and y, calculated tables and using a slicer as the way to add columns, etc. 

 

All the usual patterns that work for measures do not work for calculated tables when I tried. 

Any ideas?

2 REPLIES 2
amitchandak
Super User
Super User

@scrawfor , Final outcome is not clear. But a calculated table can not use slicer values. On a var table inside a measure can be dynamic

Thanks for the reply Amit. I partially solved the problem. Field parameters were the solution.

 

Sample Data:

scrawfor_0-1654604577135.png

 

Set up two identical Field Parameter tables, one for the x-axis and one for the y-axis:

scrawfor_1-1654604741916.png

 

Created Scatterplot visual and put the "columns" in the x and y-axis fields of the visual.

scrawfor_4-1654604908600.png

scrawfor_2-1654604779074.png

 

scrawfor_3-1654604813069.png

 

 

Only thing left is to set up the regression. But I cannot refer to the data that underlies the field parameter columns. I need to create measures for the regression calculations {e.g. SUMX(X-Axis, X-Axis[X-Axis]), SUMX(X-Axis, X-Axis[X-Axis] ^ 2), etc.} but the relevant columns of the field parameter tables are strings. Is there any to access the data that the field parameter is referring to? I tried adding these parameter fields to an add-in visual that performs regression calculations and it works so there is a way to access the underlying data set to perform the calculations. I can't use the add-in because I don't have a license and the dataset is limited to 1000 points in the free version. 

 

Any suggestions?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors