DAX - extract a column from SUMMARIZECOLUMNS

[AUaero]

Hi,

I'm having trouble with this one.  I want to extract a column from a table variable created using SUMMARIZECOLUMNS and then calculate the median of the values in that column.  I've got the following that returns the table:

EVALUATE

VAR MedianTable =
SUMMARIZECOLUMNS(
    Order_Taken_By[USER_NAME],
    "Unique Customers", Customers[UNIQUE_CUSTOMERS],
    "Unique Customers Rank",
        RANKX(
            ALLSELECTED(Order_Taken_By[USER_NAME]),
            Customers[UNIQUE_CUSTOMERS]
        )
    )
    
RETURN MedianTable

Now I want to use the MEDIAN function to get the median values for the "Unique Customers" and "Unique Customers Rank" column.  When I replace the RETURN line in the code above with the following:

RETURN MEDIAN(MedianTable[Unique Customers Rank])

I get the following error "Table variable 'MedianTable' cannot be used in current context because a base table is expected."

Any ideas on how get the column from the table variable and pass it to the MEDIAN function?

Thanks!

[v-danhe-msft]

Hi @AUaero ,

Could you please post some simple sample data and your desired result to have a test if possible? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Daniel He

[AUaero]

Hi, I have a measure working as follows:

VAR SummaryTable = 
ADDCOLUMNS(
	SUMMARIZE(
        FILTER(
            ALL(Order_Taken_By),
            Order_Taken_By[USER_NAME] <> BLANK() &&
            Order_Taken_By[JOB_TITLE] = "Sales Person"
        ),
		Order_Taken_By[USER_NAME]
	),
	"Value", 
	CALCULATE(
		Customers[UNIQUE_CUSTOMERS],
		FILTER(
			ALL('Calendar'), 
			'Calendar'[PREVIOUS_QTR_FLAG] = "PREVIOUS QUARTER"
		)
    )
)

RETURN MEDIANX(SummaryTable, [Value])

[Mi2n · Accepted solution]

Please check if the below post helps.

https://community.powerbi.com/t5/Desktop/DAX-Is-it-possible-to-refer-to-columns-of-a-table-variable/...

-Mi2n