Forum Discussion
Relationships Between Rows and Colums
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?
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
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
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.
