Forum Discussion

Frequent Visitor

4 months ago

Solved

SUMMARIZE, VALUES, and other table generation/manipuation with field parameters

Good day,

I have a need to do a correlation coefficient, linear fit, or some other description for the points in a scatter chart, but the Values field bucket uses a field parameter of over 70 possible columns. I would like to use a function like SUMMARIZE, VALUES, or some other kind of table manipulation function to generate a table for my field parameter.

For example, if I wanted to generate the correlation coefficient of metrics [metric_x] and [metric_y] by generation, I might do something like this (from quick measures):

metric_x and metric_y correlation for generation = 
VAR __CORRELATION_TABLE = VALUES('DIM_Generation'[Generation])
VAR __COUNT =
	COUNTX(
		KEEPFILTERS(__CORRELATION_TABLE),
		CALCULATE([metric_x] * [metric_y])
	)
VAR __SUM_X = SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE([metric_x]))
VAR __SUM_Y = SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE([metric_y]))
VAR __SUM_XY =
	SUMX(
		KEEPFILTERS(__CORRELATION_TABLE),
		CALCULATE([metric_x] * [metric_y] * 1.)
	)
VAR __SUM_X2 =
	SUMX(
		KEEPFILTERS(__CORRELATION_TABLE),
		CALCULATE([metric_x] ^ 2)
	)
VAR __SUM_Y2 = SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE([metric_y] ^ 2))
RETURN
	DIVIDE(
		__COUNT * __SUM_XY - __SUM_X * __SUM_Y * 1.,
		SQRT(
			(__COUNT * __SUM_X2 - __SUM_X ^ 2)
				* (__COUNT * __SUM_Y2 - __SUM_Y ^ 2)
		)
	)

Is there a way for me to set up __CORRELATION_TABLE to use my field parameter instead of DIM_Generation? I don't want to have to maintain a SWITCH statement for each possible table, which would make maintaining the measures tedious as I may add more fields to my field parameter. Something like this?

metric_x and metric_y correlation for field parameter = 
VAR __CORRELATION_TABLE = VALUES(MAGIC_DAX_FUNCTION('Field_Parameter'[Field Parameter]))
VAR __COUNT =
	COUNTX(
		KEEPFILTERS(__CORRELATION_TABLE),
		CALCULATE([metric_x] * [metric_y])
	)
VAR __SUM_X = SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE([metric_x]))
...

DAX

Field parameters

mizan2390
4 months ago
hi Matt_Sarb
As DAX cannot dynamically resolve column references for table generation, you have two option.
Option 1: The Data Modeling Fix
If you are trying to group by 70 different columns dynamically, your data model is likely too wide. The true Power BI best practice is to fix this upstream in Power Query by Unpivoting those 70 columns.
Go to Power Query.
Select your 70 attribute columns.
Choose Unpivot Columns.
This creates two new columns: Attribute (the names of your 70 columns) and Value (the actual data points, like "Generation 1").
You no longer need a Field Parameter or a SWITCH statement. You simply put the new Attribute column in a slicer, and your DAX becomes permanently simple:
VAR __CORRELATION_TABLE = VALUES('UnpivotedTable'[Value])

Option 2: The Tabular Editor Script
If you are locked into your current data model and absolutely cannot unpivot, you must use a SWITCH statement. However, you do not have to type it by hand.
You can use a free tool like Tabular Editor to run a C# script that instantly generates the 70-condition SWITCH statement for you.
// Run this in Tabular Editor's Advanced Scripting window
var sb = new System.Text.StringBuilder();
sb.AppendLine("VAR __CORRELATION_TABLE = ");
sb.AppendLine("SWITCH (");
sb.AppendLine(" SELECTEDVALUE ( 'Field_Parameter'[Field Parameter] ),");

// Assuming your 70 columns are in a table called 'Dimensions'
foreach(var col in Model.Tables["Dimensions"].Columns)
{
sb.AppendLine($" \"{col.Name}\", VALUES('Dimensions'[{col.Name}]),");
}

sb.AppendLine(" BLANK()");
sb.AppendLine(")");

// Output the code to a text box or save directly to a measure
System.Windows.Forms.MessageBox.Show(sb.ToString());
If this solves your problem, please mark this as solution and give a kudos.

6 Replies

mizan2390
Super User
4 months ago
hi Matt_Sarb
As DAX cannot dynamically resolve column references for table generation, you have two option.
Option 1: The Data Modeling Fix
If you are trying to group by 70 different columns dynamically, your data model is likely too wide. The true Power BI best practice is to fix this upstream in Power Query by Unpivoting those 70 columns.
Go to Power Query.
Select your 70 attribute columns.
Choose Unpivot Columns.
This creates two new columns: Attribute (the names of your 70 columns) and Value (the actual data points, like "Generation 1").
You no longer need a Field Parameter or a SWITCH statement. You simply put the new Attribute column in a slicer, and your DAX becomes permanently simple:
VAR __CORRELATION_TABLE = VALUES('UnpivotedTable'[Value])

Option 2: The Tabular Editor Script
If you are locked into your current data model and absolutely cannot unpivot, you must use a SWITCH statement. However, you do not have to type it by hand.
You can use a free tool like Tabular Editor to run a C# script that instantly generates the 70-condition SWITCH statement for you.
// Run this in Tabular Editor's Advanced Scripting window
var sb = new System.Text.StringBuilder();
sb.AppendLine("VAR __CORRELATION_TABLE = ");
sb.AppendLine("SWITCH (");
sb.AppendLine(" SELECTEDVALUE ( 'Field_Parameter'[Field Parameter] ),");

// Assuming your 70 columns are in a table called 'Dimensions'
foreach(var col in Model.Tables["Dimensions"].Columns)
{
sb.AppendLine($" \"{col.Name}\", VALUES('Dimensions'[{col.Name}]),");
}

sb.AppendLine(" BLANK()");
sb.AppendLine(")");

// Output the code to a text box or save directly to a measure
System.Windows.Forms.MessageBox.Show(sb.ToString());
If this solves your problem, please mark this as solution and give a kudos.

pcoley

Super User

4 months ago

Matt_Sarb, please use DAX User-Defined Functions (UDF):

1. Create the UDF (once)

In DAX Query View, define this:

/// Pearson Correlation Coefficient
FUNCTION Correlation = 
    ( MeasureX AS NUMBER, MeasureY AS NUMBER, GroupTable AS TABLE ) =>

VAR __COUNT   = COUNTX( GroupTable, MeasureX * MeasureY )
VAR __SUM_X   = SUMX( GroupTable, MeasureX )
VAR __SUM_Y   = SUMX( GroupTable, MeasureY )
VAR __SUM_XY  = SUMX( GroupTable, MeasureX * MeasureY )
VAR __SUM_X2  = SUMX( GroupTable, MeasureX * MeasureX )
VAR __SUM_Y2  = SUMX( GroupTable, MeasureY * MeasureY )

RETURN
    DIVIDE(
        __COUNT * __SUM_XY - __SUM_X * __SUM_Y,
        SQRT( (__COUNT * __SUM_X2 - __SUM_X^2) * (__COUNT * __SUM_Y2 - __SUM_Y^2) )
    )

2. Create Dynamic Measures (still needed)

Selected X = 
SWITCH( TRUE(),
    MAX('Field Parameter'[Field Parameter]) = "Metric A", [Metric A],
    MAX('Field Parameter'[Field Parameter]) = "Metric B", [Metric B],
    // ... add the rest (generate via Excel if 70+)
    BLANK()
)

Do the same for Selected Y.

3. Final Measure

X vs Y Correlation = 
    Correlation( [Selected X], [Selected Y], VALUES('Your Grouping Table'[Grouping Column]) )

UDFs remove the repeated correlation code.

pcoley
Super User
4 months ago
Instead of a giant SWITCH in a normal measure, you can create a Calculation Group that acts like a dynamic selector for your metrics. This is cleaner for maintenance when you have many measures:
Enable the feature (if not already)
Go to Model view → Calculation group button in the ribbon (or use Tabular Editor — recommended for large numbers).
Create a new Calculation Group
Name it something like Metric Selector.
Create one Calculation Item per metric (this replaces the big SWITCH)
For each of your 70 metrics, add a Calculation Item.
Example for a few items:
Calculation Item Name Expression
Metric A SELECTEDMEASURE() (or directly [Metric A])
Metric B [Metric B]
Metric C [Metric C]
... ...

Most items will simply be: [Your Actual Measure Name]
How to use it in your report
Put the Metric Selector column (from the calculation group) into the Values of your visual (or as a slicer).
The user selects which metric they want via slicer.
In your Correlation measure, you can now reference it more dynamically.

If you used the correlation UDF that i mentioned before, your final correlation measure becomes:
X vs Y Correlation = Correlation( [Selected X from CG], // or directly use the CG [Selected Y from CG], VALUES('Your Grouping Table'[Grouping Column]) )
You can even put the Calculation Group directly in the visual alongside your grouping dimension.
oussamahaimoud
Memorable Member
4 months ago
Hi Matt_Sarb,

When Power BI creates a Field Parameter, it generates a calculated table with three columns, one of which holds the display values of the selected field. You can exploit this directly.
Instead of trying to resolve the column dynamically, iterate over the Field Parameter table itself, it already contains one row per visible value of the currently selected field:
metric_x and metric_y correlation =
VAR __CORRELATION_TABLE =
SUMMARIZE(
'Field_Parameter',
'Field_Parameter'[Field Parameter]
)
VAR __COUNT =
COUNTX(
KEEPFILTERS(__CORRELATION_TABLE),
CALCULATE([metric_x] * [metric_y])
)
VAR __SUM_X =
SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE([metric_x]))
VAR __SUM_Y =
SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE([metric_y]))
VAR __SUM_XY =
SUMX(
KEEPFILTERS(__CORRELATION_TABLE),
CALCULATE([metric_x] * [metric_y] * 1.)
)
VAR __SUM_X2 =
SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE([metric_x] ^ 2))
VAR __SUM_Y2 =
SUMX(KEEPFILTERS(__CORRELATION_TABLE), CALCULATE([metric_y] ^ 2))
RETURN
DIVIDE(
__COUNT * __SUM_XY - __SUM_X * __SUM_Y * 1.,
SQRT(
(__COUNT * __SUM_X2 - __SUM_X ^ 2)
* (__COUNT * __SUM_Y2 - __SUM_Y ^ 2)
)
)

This only works correctly if your Field Parameter fields are all from tables that have an active relationship feeding into your fact table. If a selected field is from a disconnected table, CALCULATE inside the iterator won't propagate the filter to your metrics, that's a data model issue, not a DAX issue, and no amount of DAX will fix it without a relationship or TREATAS.
v-prasare
Community Support
4 months ago
Hi Matt_Sarb,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support
v-prasare
Community Support
4 months ago
Hi @Matt_Sarb,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

Forum Discussion

SUMMARIZE, VALUES, and other table generation/manipuation with field parameters

Option 1: The Data Modeling Fix

Option 2: The Tabular Editor Script

6 Replies

Option 1: The Data Modeling Fix

Option 2: The Tabular Editor Script

1. Create the UDF (once)

3. Final Measure

Related content

Parameter in summarize

Parameter table dimensions - summarize

Don't Summarize....still summarizes...?

GroupBY and summarize

Summarize based on field parameter

Metric A	SELECTEDMEASURE() (or directly [Metric A])
Metric B	[Metric B]
Metric C	[Metric C]
...	...