Relationships Between Rows and Colums

[Claire_October]

I have two data sets:

1. Geographic location table which has three columns: Lat, Long, UID. I have used this to generate a map using the ArcGIS visualisation tool.

2. A table which contains a time versus quantity series for each UID. This has several hundred columns; one for time, and one for each UID.

See example:

I would like to be able to select the UID in the ArcGIS window and by doing so, bring up a plot of the time series data for that node. My issue is that the UID is in rows for the ArcGIS table and columns for the time series data.

Is there a way of linking the two?

Many thanks,

Claire

[o25840]

Good day, I have a issue that relates to this tread however when applying this recommendation, then it disrupts the rest of my measures. When you unpivot a column then it duplicates the line entry. Are there any other options to achieve the same result? See below

The objective is to create a radar chart. The issue is that the dimension / category does not exist in the main table but there is a outcome value for the category in the main table. I have created a second table with the category but not able to map the row and the column. Any suggestions?

[dm-p]

Based on how I interpret it, I'd do the following. note that as your data is an image, I've manually entered a small portion of it, but the principle should be the same.

• Create a query for your coordinates/UIDs (we'll call this UID), e.g.:
  
  
  
  
• Create another query for your second table (we'll call this Time)
  
  
• Once the data has been loaded, highlight the Time column, select Unpivot Data and Unpivot Other Columns, e.g.:
  
  
  
  
• This will now create one row per time per UID, e.g.:
  
  
  
  
• Rename the Attribute column to UID.
  
  
• Cchange the data type of the Value column to Decimal Number and then Close & Apply.
  
  
• Join the UID table to the Time table on the UID column (Power BI may well have already done this for you), e.g.:
  
  
  
  
• I created a line chart, with Time on the Axis, Value under Values, and UID (from the UID table, although it shouldn't matter) for Legend. This will now cross-filter when clicking on a coordinate in the ArcGIS visual, e.g.:
  
  

This may not be exactly what you're after but might get you close to where you need to be?

[Claire_October]

Thank you very much for your reply - really appreciate your time. I followed all your steps and got exactly what I wanted. However, one thing I did notice was that if I use my full data set which has 733 columns for the original time series, I got an error saying that the relationships could not be created. I then trimmed the data so that I only had 24 columns and it worked perfectly. Are you aware of any kind of data size limit for this method?

Claire

[Claire_October]

As a follow up to this, I have tested some different sized data sets with UIDs which are different from those in the dataset which worked yesterday and I get an error even with 20 columns so I am now wondering whether there is some specific issue within the data set.

The specific error is:

Column 'Time' in Table 'Time' contains a duplicate value '80.583' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

I have checked that all my UIDs are unique. The 'Value' column does not contain unique values but then this was also the case for the dataset which worked!

Many thanks,

Claire

[dm-p]

It sounds to me like the two tables are being joined on the incorrect column. As the Time column should contain your measures, duplicates would be okay here. My suspicion is that Power BI is attempting a join on this column, which is not correct (and it might be due to naming conventions between both tables).

You might want to edit the relationship to confirm that it's joining on the UID columns (they should be higlighed in both tables). If not, then update the join to specify these two columns, and this should be correct.

If it's not this, then it going to be easier to help if it's possible to get a copy of your report, as it's difficult to tell what other factors might be at play.