Create a table and copying a column from a dataset in a DirectQuery local model

[DonRitchie]

This seems more complicated than it should be.

I am connected to a local model of a dataset.  There is a table "SALES" with a column "CUSTOMER".  I select New Table under Modeling ribbon.

DATE = SELECTCOLUMNS(SALES,"Customers", SALES[CUSTOMER]) returns an error: The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.

DATE = SELECTCOLUMNS(SALES,"Customers", DISTINCT(SALES[CUSTOMER])) returns an error:OLE DB or ODBC error: Query (3, 40) A table of multiple values was supplied where a single value was expected. Technical Details: RootActivityId: 3f5fb32a-a5b8-4738-9237-06a50d9075dc Date (UTC): 8/8/2022 4:24:54 PM.

What am I doing wrong?

[foucaultf · Accepted solution]

Hi @DonRitchie ,

Please note: SELECTCOLUMNS function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Alternatively, you can try import mode to connect data.

For more information, please refer to: SELECTCOLUMNS function (DAX) - DAX | Microsoft Docs

Best Regards

[DonRitchie]

How do I import a dataset I don't have access to alter?

[foucaultf]

Please select import option. Then you can use DAX without limitations.

[amitchandak]

@DonRitchie , Try like

DATE = summarize(SALES,"Customers", SALES[CUSTOMER])

My suggestion would be better get from database as import , using SQL in advance connection option