Latest Enterprise DNA Initiatives


Show Data & Data Labels in Visualizations

I have my raw data and data dictionary in two separate files (included here in one Excel file under separate Tabs) and structure. The raw data has a regular/normal structure with variables on the column and subjects on the rows. However, the data dictionary file includes a list of variables on rows under one column (Variable Names) followed by corresponding columns that describe each variable such as variable labels, value labels, variable type, etc. Since the two structures are different, I can’t connect the two tables to bring variable characteristics from Data Dictionary to any visualizations created via the raw data set. The only option, to my knowledge, is to Unpivot the raw data set so all variables are listed under one column then I can join the two tables as 1 to many relationships. Since the raw data set contains 5000 rows and more than 800 variables, Unpivoting the data set creates millions of rows and not an efficient solution. The desired outcome, indicated in Power BI example (included), is when I hover over a variable in the Matrix, or in any other visualizations, I like to see its labels and all other information existed in the corresponding rows of data dictionary. Any guidance is greatly appreciated.

Thank you,

Helal
Data and Labels.pbix (68.6 KB)
Data and Labels.xlsx (21.5 KB)


Data and Labels.pbix (69.5 KB)

Hi Helal,

Kindly check the attached solution and let me know if your issue solved

Thank you so much. It did work. However, as I mentioned in my post unpivoting tables is one option that I don’t want to consider since as I explained in the post it will generate millions of records and slows down performances significantly. This is what I mentioned in my post “The only option, to my knowledge, is to Unpivot the raw data set so all variables are listed under one column then I can join the two tables as 1 to many relationships.
Since the raw data set contains 5000 rows and more than 800 variables, Unpivoting the data set creates millions of rows and not an efficient solution.”
Any other solution you may think of?

Helal

HI @Helal - Unpivoting is the only option that can work here considering the data set and the requirements. Please close this post if no other information required.

Thank you @ankit I will close the post then.