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

Can't build a comparison table due to the multiple relations

Hi everyone,

 

I want to create a table in order to compare two values from separate datasets. The datasets are shown in the below picture.

datasets.png

I don't have any problems with the datasets individually but creating a single table not seems possible. To create a relation in order to create a single table, I related the datasets through the date data. 

Relations.png

That helped me to get the two datasets to a single table but now some of the parameter values (highlihted values in the below picture) are duplicated for other equipments. I know additional relation between the equipments are required but I couldn't manage to build a relationship.  I created a reference table for the equipments and relate it to the both datasets but again that did not help.

Table.png

Do you have any suggestions to create a relationship between the equipments table? Thank you for your time.

 

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @Anonymous ,

 

You may consider use merge queries in power query editor:

transform data -->merge queries as new --> expand column and rename columns

vcgaomsft_0-1673500614830.png

vcgaomsft_1-1673500663204.png

vcgaomsft_2-1673500711679.png

vcgaomsft_3-1673500795743.png

result:

vcgaomsft_5-1673500934012.png

Or please new a measure like:

 

 

Second  dataset = 
VAR _date= MAX('Table1'[Date])
VAR _equipment = MAX('Table1'[Equipment])
VAR _result = CALCULATE(SUM('Table2'[Parameter]),'Table2'[Date]=_date&&'Table2'[Equipment]=_equipment)
RETURN
_result

 

 

vcgaomsft_6-1673501181808.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

View solution in original post

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

Hi @Anonymous ,

 

You may consider use merge queries in power query editor:

transform data -->merge queries as new --> expand column and rename columns

vcgaomsft_0-1673500614830.png

vcgaomsft_1-1673500663204.png

vcgaomsft_2-1673500711679.png

vcgaomsft_3-1673500795743.png

result:

vcgaomsft_5-1673500934012.png

Or please new a measure like:

 

 

Second  dataset = 
VAR _date= MAX('Table1'[Date])
VAR _equipment = MAX('Table1'[Equipment])
VAR _result = CALCULATE(SUM('Table2'[Parameter]),'Table2'[Date]=_date&&'Table2'[Equipment]=_equipment)
RETURN
_result

 

 

vcgaomsft_6-1673501181808.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Anonymous
Not applicable

That worked! Thank you so much 

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.