Connecting column headers to row information.

[wolfgang87]

Hello everyone, 

I've two tables like below, where the on is an fact table with data from measuring with sensors and the second table is a dimension table with informations to the sensors. Imagine, that the fact table has over 3000 rows. My goal is to get a Matrix visual or writing DAX query to get a table like table 3, where i have calulated measure values from the fact table combined with further information from the dimenstion table.

Is it possible with dax query or is there a better way?

Thx for any help.

[Anonymous]

Hi all,thanks for the quick reply, I'll add more.

Hi @wolfgang87 ,

Please follow these steps:

1.Unpivot your fact table in Power Query.

2.Establishing relationships between tables

3.Final output

Best Regards,
Wenbin Zhou

[danextian]

Hi @wolfgang87 

As what @uzuntasgokberk  has suggested, you need to unpivot your fact table. Select datetime, right click and select Unpivot other columns which should create two new columns - one for the header called attribute and another one for the values.

[uzuntasgokberk · Accepted solution]

Hello @wolfgang87 ,

You need to unpivot fact table first of all and the table shoud be like this column: Datetime, Attributes(shoud be m1,m2,m3) and values(should be m values). And make relationship between fact and dimension(id to atribute(1 to many)). And create a measure gives average which is AVERAGE(table[Values]).

Kind Regards,
Gökberk Uzuntaş

📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |