DirectQuery issues creating new table or calculated dimension

[alexa_0028]

My client is using a direct query to build visualization on top of our datasets and they cannot import query as the dataset is huge.
So, now they want to create a table that gets selected columns from the existing measure table.I tried a couple of approaches and they failed:
Approach 1: I created the concatenated new column in the source measure table like below -
Concatenated_Column_key = 't1'[sk_country]&'t1'[sk_customer]&'t1'[sk_product]
But when I am creating a new table and adding this column to that table I am getting error like-

The column Concatenated_Column_key cannot be pushed to the remote data source and cannot be used in this scenario

Approach 2: I tried directly creating the table using below DAXS but I am getting an error that "The resultset of a query to external data source has exceeded the maximum allowed size of ‘1000000’"
New_Table = DISTINCT(SELECTCOLUMNS('t1',"Concatenated_Column_key ",'t1'[sk_country]+'t1'[sk_customer]+'t1'[sk_product])

Can you pls help me with how can I achieve this?

Overall goal: I want a graph of Account Status v/s Sales Growth %.  The sales growth % is the measure in table "t1" but the account status is a calculated dimension that I want to create over the Concatenated_Column_key.

DASx for Account Status:(If I try to create this dimension directly in t1 I am getting the same The resultset of a query to external data source has exceeded the maximum allowed size of ‘1000000 and my graph is getting broken)

1. Account status =
2. SWITCH(
3. TRUE(),
4. t1'[Sales 2021]=0,"Inactive" ,
5. t1[Sales 2020]>0,"Lost",
6. t1[Sales Growth %]>=0.8,"Gained",
7. t1[Sales Growth %]>=0.05,"Gaining",
8. t1[Sales Growth %]<=-0.05,"Losing",
9. "Stable"
10. )

Thanks in advance for your help !!

[v-xulin-mstf]

Hi @alexa_0028,

Is your issue solved?

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[v-xulin-mstf]

Hi @alexa_0028,

What is the calculation logic of Concatenated_Column_key?

If I understand correctly, what you want is a new text column and a measure from 't1'.

Could you provide your data structure and expected output?

Best Regards,
Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.