Median of multiple columns in different tables

[MorePowerBI]

Hi everyone,

Is there a way to obtain the median value of two columns in different tables?

Table1 (left) and Table2 (right) - the Age column contains blank values (they're just filtered from this view)

Trying to find the median of the two Age columns which would be 49.

Is this doable without merging the table in Power Query editor?

[Anonymous · Accepted solution]

Hi @MorePowerBI ,

This measure should work.

Measure = 
var _union = UNION(SELECTCOLUMNS(FILTER(Table1,Table1[_SDNCEAge]<>BLANK()),"age",Table1[_SDNCEAge]),SELECTCOLUMNS(FILTER(Table1,Table1[_SDNCEAge]<>BLANK()),"age",Table1[_SDNCEAge]))
return
MEDIANX(_union,[age])

Best Regards,

Jay

[lbendlin]

Yes, sorry, the second argument is the field name from the first table.

[MorePowerBI]

Tried this but I get an error saying it can't find the column

RETURN

MEDIAN([_SDNCEAge])

I also CombinedTable[_SDNCEAge] but got the same error.

[lbendlin]

Might need to add a SELECTCOLUMNS  to rename them.

Please provide sanitized sample data that fully covers your issue. Not as a screenshot, please paste as text.

[lbendlin]

Use the UNION() operator to append the tables and then calculate the median from that union.

[MorePowerBI]

The two tables don't have the same number of columns.

EDIT:
I created a calculated table below:

CombinedTable=

FILTER (

UNION( VALUES(Table1[_SDNCEAge]), VALUES(Table2[_NCEAgeSP]) ),

NOT ISBLANK ( [_SDNCEAge] )

)

I can obtain the median this way but is there a way to do this without creating a calculated table? (i.e. store the CombintedTable as a variable in a measure and return the median)

[lbendlin]

yes, you can assign the result to a table variable

Measure = 
var CombinedTable=
FILTER (
UNION( VALUES(Table1[_SDNCEAge]), VALUES(Table2[_NCEAgeSP]) ),
NOT ISBLANK ( [_SDNCEAge] )
)
return medianx(CombinedTable)

or if you like it compact

Measure = medianx(
FILTER (
UNION( VALUES(Table1[_SDNCEAge]), VALUES(Table2[_NCEAgeSP]) ),
NOT ISBLANK ( [_SDNCEAge] )
)
)

[MorePowerBI]

I tried those but neither work. I get an error that says MEDIANX() requires a minimum of 2 arguments. I also tried MEDIAN() but then it tells me that this function only takes columns as arguments (since the VAR is a table)