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?

Use TOP() instead of INDEX().

See also:

1 Like