CORREL OpenBI
The correlation between two expressions over a table's rows (Pearson's); BLANK for fewer than two rows or no variation.
Group: Statistics. OpenBI’s own: DAX has no function of this name.
Syntax
CORREL ( <table>, <x>, <y> )| Parameter | Type | Description |
|---|---|---|
table | Table | The rows to go through. |
x | Expression evaluated for each row | The first number, worked out for each row. |
y | Expression evaluated for each row | The second number, worked out for each row. |
Returns
Number. The Pearson correlation coefficient between x and y, from −1 to 1.
Remarks
- Only rows where both x and y have a value count; a row with a blank in either is left out.
- With fewer than two such rows, or when x or y is the same on every row, the result is BLANK.
- Text is an error; dates count as their serial numbers.
- It measures how well a straight line fits. For a relation that rises steadily but bends,
CORREL.SPEARMANsays more.
Compared with DAX
An OpenBI function: DAX has none like it, so a measure or query that uses it works in OpenBI only, not in Power BI or Analysis Services.
In Power BI the same coefficient takes a measure written out from sums of products and squares.
Examples
Warmer days, more beer
EVALUATE
VAR Days =
DATATABLE (
"Temperature", INTEGER, "Liters", INTEGER,
{ { 8, 380 }, { 12, 410 }, { 15, 470 }, { 19, 455 }, { 23, 590 }, { 26, 610 } }
)
RETURN
ROW ( "Correlation", CORREL ( Days, [Temperature], [Liters] ) )| Correlation |
|---|
| 0.950661297911026 |
Population and GDP by continent
A continent with a single country has no correlation.
EVALUATE
SUMMARIZECOLUMNS (
Countries[Continent],
"Countries", COUNTROWS ( Countries ),
"Correlation", CORREL ( Countries, Countries[People], Countries[GDP] )
)
ORDER BY Countries[Continent]| Countries[Continent] | Countries | Correlation |
|---|---|---|
| Africa | 51 | 0.820871122295425 |
| Asia | 47 | 0.781768427189445 |
| Europe | 39 | 0.792967830668549 |
| North America | 18 | 0.950765041991018 |
| Oceania | 7 | 0.948472070437285 |
| Seven seas (open ocean) | 1 | (blank) |
| South America | 13 | 0.983989902077897 |