Include columns with different values in the same matrix

[Avivek]

My requirement is to give the data as shown below:

The columns mentioned in respective consignment, loaner and others are there in the model. Is there any way to present them in the same matrix as in the above image ?

[vanessafvg]

hi its not very clear what you are asking, do you want to join this data? , are they in the same tables? if not, what is the joining fields between the tables. If the data is joined you can add them together in the same matrix.

[Avivek]

Sorry, I was not very clear.

For eg. Consignment is a table, Loaner is a table Exp Usage is a separate table and so on. Now under Consignment completed, last 2 months,proj,total proj are different columns. Same way for Loaner completed, last 2 months,proj,total proj are separate columns. 

Is there a way we can have them in the same matrix and show the respective values. Because if I add the columns it would show the value for all columns.

[Anonymous]

Hi @Avivek 

You need to build relationships between the tables you need to add into matrix.

If you have build relationships, could you show me the relationships between tables? And can you show me your issue Screenshot and show me which table you have added into matrix? You can provide me your data model as well, or you can share your pbix file with me by your Onedrive for Business.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Avivek]

The relations are already created, please find the image below

The table consignment has the columns Completed,last 2 months, proj, total proj, similarly we have same columns for loaner as well and so on.

Is it possible or is there some function by which I can get this.

[Anonymous · Accepted solution]

Hi @Avivek 

Here I have some advice.

Due to I don't know the data model of your tables, I build two sample tables to have a test.

Consignment Table:

Loaner Table:

Use unpivot and add index in two tables:

Result:

Add two calculated columns in two new tables and build matrix(Trun off the word wrap in column headers and reduce the row header of Loaner to 0).

Column = RANKX(FILTER(Consignment,Consignment[Attribute]=EARLIER(Consignment[Attribute])),Consignment[Index],,ASC)

Column = RANKX(FILTER(Loaner,Loaner[Attribute]=EARLIER(Loaner[Attribute])),Loaner[Index],,ASC) 

Result:

If this reply still couldn't help you solve your problem please provide a sample pbix file by your OneDrive for business which has the same date models like yours.

You can download the pbix file from this link:  Include columns with different values in the same matrix

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.