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
Anonymous
Not applicable

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
Anonymous
Not applicable

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 @Anonymous   ,

 

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

Anonymous
Not applicable

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:)

v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

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
Super User

@Anonymous - 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

 

@Anonymous 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.