topic Re: Create a table and copying a column from a dataset in a DirectQuery local model in DAX Commands and Tips

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

DonRitchie — Mon, 08 Aug 2022 16:27:30 GMT

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?

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

amitchandak — Tue, 09 Aug 2022 04:27:22 GMT

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

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

foucaultf — Thu, 11 Aug 2022 10:04:51 GMT

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

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

DonRitchie — Thu, 11 Aug 2022 10:11:24 GMT

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

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

foucaultf — Fri, 12 Aug 2022 01:38:30 GMT

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