Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alexa_0028
Resolver II
Resolver II

DirectQuery issues creating new table or calculated dimension

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 !!

2 REPLIES 2
v-xulin-mstf
Community Support
Community Support

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
Community Support
Community Support

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.