Related() doesn't show active related table.

[GSP_IPL]

I have a table "inventory" that contains unique values that is pulling data from other related tables.

Most relationships are 1:1 and some are *:1

Getting data from any of those related tables works fine except in one instance.

I have a table "ABCReport" (Many) in an active relationship with "Inventory" (One)

When using function related() to set up a new column in Inventory, autofill does not show the related table.

If i were to create a new column in the ABCReport table (which contains duplicate values), the autofill recognises the relationship to inventory and shows it in autofill.

Also ABCreport table can access any related table that is linked to Inventory.

The relatioship arrow in directions points towards towards the many table "ABCReport" if that makes any differance.

I switched the filter direction to both but it didn't work.

I have even removed/re-imported the table, created a relatedtable() and used that for the relationship but still nothing.

Using the related() function inside Inventory table refuses to show the relationship connection to ABCReport

Any help would be apreciated.

Thank you

Thank you

[parry2k · Accepted solution]

Yes as I explained how RELATED function works. If you had read my previous reply carefully, I asked what you are trying to achieve from ABCReport, there might be another alternate solution but you cannot use related on one side

Instead of getting into RELATED and stuff, I think it will make more sense (as previously mentioned), explain what you are trying to achieve with the sample data. If you cannot do it, not sure how I or anyone else can help. Good luck!

[GSP_IPL]

I see my mistake now.

Thank you for pointing it out

[parry2k]

@GSP_IPL In other words, you cannot use related in the inventory table to get columns from abcreport table because abcreport is on many side. 

If that is not the case then maybe I'm still missing something or not clear 

[GSP_IPL]

That is the case, i can't use related() in inventory to pull data from abcreport.

[parry2k]

@GSP_IPL you are contradicting your own problem statement, "ABCReport" is many side, and inventory is on one side and this is what I replied:

If all the above is correct, you cannot use related in the inventory table because related works from many to one side, not one to many side. You can check this video on my channel: Let's explore how I used RELATEDTABLE and RELATED DAX Functions - YouTube

[parry2k]

@GSP_IPL If I understood correctly, you want to add a column on one side of the relationship, in this case, you want to add something in the inventory table from abcreport table, if that is the case and my understanding is correct, what actually you want from the abcreport table. 

If all the above is correct, you cannot use related in the inventory table because related works from many to one side, not one to many side. You can check this video on my channel: Let's explore how I used RELATEDTABLE and RELATED DAX Functions - YouTube

Regardless, if you can explain your problem with some sample data and the expected result, it will help to provide a solution.

[GSP_IPL]

Sorry, You misunderstood what i wrote.

I'm trying to use related from Many to One

not the other way around.

I have already used the function in the "inventory" table in multiple columns linking to other tables and works fine.

For some reason the table "ABRReport" won't show up as a related table when called with related() from within the "Inventory" table

[sevenhills]

I think LookupValue will suffice for your new column needs. Add a column and use like below. 

DeliveryYear =
    LOOKUPVALUE(
        'Date'[Year],
        'Date'[Date], Sales[DeliveryDate]
    )

If not, try using RELATED and USERELATIONSHIOP like mentioned in this article.
Check this article. https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

[GSP_IPL]

Unfortunatelly, Lookupvalue doesn't really suit my needs as the table i'm pulling from has duplicate values. I'm also doing more than just pulling values.

The related() method is only part of my overall function.

I can already achieve what i want using calculate() and a whole bunch of filters. but i like everything optimized and thus want to figure out a solution to this.

[sevenhills]

See if any of these syntaxes help you 

DeliveryYear =
CALCULATE (
    SELECTEDVALUE ( 'Date'[Year] ),
    CALCULATETABLE (
        Sales,
        USERELATIONSHIP ( Sales[DeliveryDate], 'Date'[Date] ),
        REMOVEFILTERS ( ‘Date’ )
    )
)

LOOKUPALT = CALCULATE(FIRSTNONBLANK(Table1[Value2], TRUE()), FILTER(Table1, Table1[ID] = Table2[ID]))

Lookup Rental_class = 
VAR Income = SELECTEDVALUE ( TableA[Rental_income] )
VAR Boundary =
    TOPN (
        1,
        FILTER ( TableB, TableB[Rental_boundary] >= Income ),
        TableB[Rental_boundary], ASC
    )
RETURN
    MAXX ( Boundary, [Rental_class] )

Lookup Rental_class =
VAR Income = SELECTEDVALUE ( TableA[Rental_income] )
VAR Boundary =
    CALCULATE ( MIN ( TableB[Rental_boundary] ), TableB[Rental_boundary] >= Income )
RETURN
    CALCULATE (
        VALUES ( TableB[Rental_class] ),
        TableB[Rental_boundary] = Boundary
    )

If none of the above suits your needs, the other option is doing in PQ/M Code to get the column data. (as you are looking for optimization).

[GSP_IPL]

As i mentioned above, i've already achieved what i need with calculate().

But i don't want to have multi line code on multiple columns for something that can be achieved in a single line far more efficiently with related(). 

The purpose of this post is to find what i might have done wrong for this to be happening (and learn from it) or if there is a genuine issue with the Program, to identify it and have it fixed.

Alternative ways of doing this do not interest me.

Thank you for trying to help.

[sevenhills]

Ok.