How to plot the matix table in the same table from two seperate tables?

[JulietZhu]

I need output like this.

But right now, the first row is from one table with no row header (don't know how to show row header). The 2nd and 3rd rows are from another table. How  can I plot them in the same matrix table.

Dummy table in this link, https://1drv.ms/u/s!AlYpYKwSuOKxhE8L9XLzjjEuUz0i

Thanks,

Juliet

[v-yuta-msft]

Hi JulietZhu,

Based on my analysis, relationships between table 'Encounter' and 'Date', 'Transactions' and 'Date' are both many-to-one. So rows in 'Encounter' can't match rows in 'Transactions' because they are many-to-many relationship so that you can put columns from the two tables into one chart. That's why values in [EncounterCount(last n months)] will be blank. In addtion, if you could create a one-to-many or many-to-one relationship between table 'Encounter' and 'Date', you can put columns from the two tables in the same chart.

 

 

Regards,

Jimmy Tao

[JulietZhu]

@v-yuta-msft

This is the relationship between 3 tables.  Encounter and Transaction are fact table and Date is dimetion table.

In this report, when only one date is chosen from slicer, it requires showing the past 4 months' data dynamically.

In order to do this, I have to created MonthYear colum in both encounter and transaction table separately with related function.

So encounter and transaction data can't be shown in the same table. I don’t know if solutions, such as union them together or formatting row or column header etc , will be possible. Tried and still no luck. Thanks

[Ashish_Mathur]

Hi,

That is a 141 MB file.  Please trim the file to a couple of MB's max and then share the download link.

[JulietZhu]

@Ashish_Mathur, I only pull 5 months data in and you can find new file in the link. Thanks

[Ashish_Mathur]

Hi,

That is still a 51.6MB file.  I will not be able to help.  If possible, please get the file size down to a couple of MB's.