cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
teckchan
Helper III
Helper III

Entering target data and Setting Dynamic target lines

Hey Everyone,
Hope this message finds you safe and well. On one of my report, I need to add a dynamic target line to a line chart. So I entered the target data as new table region wise. The idea is to join this target table with a main table based on region and the target line of the visual would change with the selection of a region slicer from the main table. But these numbers are of text data type by default and I get errors (dataformat.error we couldn't convert to number) when i try to change the data type to numbers. Any advice on how I should go about this?

teckchan_0-1599674157409.png

Thank you in advance.
Best,
Akash

1 ACCEPTED SOLUTION

Thank you so much everyone for all the help . As this was a small table, I tried recreating the issue again by manually entering the data and this time it read as whole number. I was able to make the joins and it works perfectly:)

View solution in original post

6 REPLIES 6
v-xicai
Community Support
Community Support

Hi @teckchan   ,

 

Do the suggestions from engineers make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

 

Best regards

Amy

Thank you so much everyone for all the help . As this was a small table, I tried recreating the issue again by manually entering the data and this time it read as whole number. I was able to make the joins and it works perfectly:)

View solution in original post

v-xicai
Community Support
Community Support

Hi @teckchan ,

 

For this errors "dataformat.error we couldn't convert to number", you  may enter into Query Editor, select on this query, go to APPLIED STEPS, find the step named "Changed type" (maybe in the 2nd, 3rd step), then you can do the data type transform which converts the data type from text to number on this step, make sure click "Replace Current" in pop up page.

 

If you need to unpivot the 4 columns to combined column, you may remove the relationship first of all, then go to Query Editor to unpivot the 4 columns. After that, you may recreate Many to Many relationship between the two tables.

 

Best Regards,

Amy 

 

Community Support Team _ Amy

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

Greg_Deckler
Super User IV
Super User IV

@teckchan - Odd, maybe try Clean and Trim operations on the columns? Also, I would unpivot those quarter columns.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hey @Greg_Deckler ,
Thanks for your quick reply. I am unable to unpivot those fields like the image below, i get an error when i try to apply changes saying :

teckchan_0-1599676529802.png

teckchan_1-1599676664256.png

But this is not the main problem, I have tried trimming and cleaning the columns, even after unpivoting in the query editor, I get errors while coverting to numbers. 
When i try to change data type in pbi desktop, it asks me this and i get erros if i click yes.

teckchan_2-1599676844209.png

 

@teckchan Remove any relationships to that table. Unpivot. Create a bridge table to bridge that table to the other table that was related to it. Did the clean and trim allow you to convert it to a number?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors