# Filtering and selecting between four tables

I have four tables, as I mentioned, there are references between them. How can I show the Table 4 data in Table 1 by filtering it according to Table 3? So how can I get report from table 1?

Table 1(Reports)[GID]

Table 2(Equipment)[TID]

Table 3 (Records)[ID].[TID].[GID]

Table 4(Analysis)[ID].[TID]

`ORDERBY(FILTER(â€śTable 4â€ť, ([_ROWNUMBER] = MIN(SELECT(Table 4[_ROWNUMBER], AND([_THISROW-1].[TID] = [TID], IN([TID], SELECT(Table 3[TID], [_THISROW].[GID] = [GID]))))))), [TID],false)`

I solved it this way. Thanks @Steve for this method.

1 Like

Is there any way to convert rows to multiple columns in reports.?

Row 1 â€¦Date1â€¦Col.
Row 2â€¦Date2â€¦Col.
Row 3â€¦Date 3â€¦Col.

Row1 Row2 Row3
Date1 Date 2 Date3
Col. Col. Col.
â€¦ â€¦ â€¦
â€¦ â€¦ â€¦
â€¦ â€¦ â€¦

Row 1
Date 1
Col.
â€¦
â€¦

Row 2
Date 2
Col.
â€¦
â€¦

Row 3
Date 3
Col.
â€¦
â€¦
I can transform as a single column.

Is there any improvement in this matter?

@Steve Hi,

`ORDERBY(FILTER(â€śTable 4â€ť, IN([TID], SELECT(Table1[TID], AND([_THISROW-1].[TID] = [TID], [_THISROW].[GID] = [GID])))), [Analysis Date],true)`

I do the outer grouping with the above formula. No problem, but there is a problem with the inner grouping, the data are mixed. How should the formula for inner grouping be?

The relationships between your tables are not clear. The expression here doesnâ€™t match the previous description of the tables.

reference columns for the second and third columns

<<Start: ORDERBY(FILTER(â€śTable 4â€ť, AND([_THISROW-1].[TID] = [TID], IN([TID], SELECT(Table3[TID], [_THISROW].[GID] = [GID])))), [Analysis Date],true)>><<[Analysis Date]>>

It is divided into groups in this way, but the same date data comes only

Can you help me please? I just couldnâ€™t.

I fixed the problem, the problem is left as Table 4 column Id column number. I did â€śUNIQUEID ()â€ť and there was no problem. The above formula works correctly. It was a very good experience for me.

1 Like

I want to get value according to the last analysis date why this formula doesnâ€™t work

<<Start: INDEX(ORDERBY(FILTER(â€śTable 4â€ť, AND([_THISROW-1].[TID] = [TID], IN([TID], SELECT(Table3[TID], [_THISROW].[GID] = [GID])))), [Analysis Date],true),1)>><<[Analysis Date]>>

``````<<If: [Analysis Date]=INDEX(ORDERBY(FILTER(â€śTable 4â€ť,  AND([_THISROW-1].[TID] = [TID], IN([TID], SELECT(Table3[TID], [_THISROW].[GID] = [GID])))), [Analysis Date],true),1)
>><<[Analysis Date]>><<EndIf>>
``````

This did not happen, what should I do, to show the date of the last analysis. can you help me?