Reference to Table Rows from Many-to-Many relation

[Markus_Malessa]

I have a Many-to-Many bridge table where I set the FK columns to Ref, which in turn automatically created a de-reference in each of my tables. This displays like this:

Ideally what I would like is rather than displaying the table with the two columns I would only like to display a table of the records on the ‘Annual Reports’ side. I attempted this by creating another virtual column in my Permits table with a formula of FILTER(“AnnualPermitData”,IN([id],[Annual Reports])). Unfortunately this did not return any records. My question is how I need to fix my formula and/or is there a more efficient way to accomplish this (something that would be less intensive from a query perspective)?

[Steve]

Try:

SELECT(
  AnnualPermitData[Annual Reports],
  IN([_THISROW], [Related Permits]),
  TRUE
)

I’m curious why a permit references the permit data rather than the other way around. If the other way around, you could just use:

[Related AnnualPermitDatas][Annual Reports]

See also:

[Markus_Malessa]

I get this now

The virtual column of ‘Annual Reports’ is contained in my PermitList table and has formula

The virtual column of ‘Related Permits’ is contained in my AnnualPermitData table and has a formula

And these are the columns in the bridge table i.e. table AnnualPermitDataPermits_Has_RelatedPermits

Hopefully that clears up some of the data structure and you see something.

[Steve]

Try:

SELECT(
  AnnualPermitDataPermits_Has_RelatedPermits[AnnualPermitDataPermits_fk],
  ([_THISROW] = [RelatedPermits_fk]),
  TRUE
)

[Markus_Malessa]

Steve:

SELECT( AnnualPermitDataPermits_Has_RelatedPermits[AnnualPermitDataPermits_fk], ([_THISROW] = [RelatedPermits_fk]), TRUE )

Hi Steve,

Thank you that certainly helped it along. This is what I ended up needing to do to give me the desired result:
FILTER(“AnnualPermitData”,
IN([id],
SELECT(
AnnualPermitDataPermits_Has_RelatedPermits[AnnualPermitDataPermits_fk],
([_THISROW] = [RelatedPermits_fk]),
TRUE
)
)
)

So the top portion is what I was getting before, but the bottom is what I actually wanted.

As a follow up though, is there a more efficient expression that I could use to get the same accomplished? I am looking at this from a resource (time intensive) perspective.