Related function not returning any values.

[Victor_AguirreV]

Hey guys!

This is my first post (of many hopefully) here, and firstly thanks for any help you can give me!

I have 2 tables, one called All Trx, and another one called  All CBs. They are related in a one to many (respectively) relationship by the fields called Trans ID on the first table, and AcuityTec ID on the second table. All tables have unique  values in the IDs, and the cross filter setup is something I have been playing around, to try to make the DAX to work, but to no avail.

The first table as it says, contains all transactions, but the second table contains only transactions that were filed as chargeback. So all transactions from the second one will show at some point in the All trx, but not the other way around.

The formula I have tried is 

Is_CB= RELATED('All CBs'[AcuityTecID]), but the intelligense doesn't pop up anything, telling me that there is no relationship between the big All Trx Table and the smaller All CBs table.

What could I do?

If you need any further context, please do let me know.

What I would like to do is to do a simple related() function, to checkk if the transaction that we have on the All trx, is found in the All CBs table. With the relationship model shown in the image, I can't get the related formula to work, and I am guessing that this has to do with the relationship type between them.

[Victor_AguirreV]

The related tihng worked!

I used the following:

Is CB =

VAR RTable = RELATEDTABLE('All CBs')

VAR FilteredTable = FILTER(RTable, 'All CBs'[AcuityTecID]= 'All Trx'[Trans ID])

RETURN

    IF (

        COUNTROWS(FilteredTable) > 0,

        MAXX(FilteredTable, 'All CBs'[AcuityTecID]),

        BLANK()

    )

This will return the ID on the other one. While I used GPT to get help on this formula, I am not sure I get what this is doing. The MAXX is in the filtered table, which I guess it would be a table with only one register? Then the MAXX would return that for each of the rows?

I apologize if this is a bit too obvious, but I am not used to using iterative functions yet.

[Daniel29195 · Accepted solution]

@Victor_AguirreV 

if you are creating a column in the table on the 1 side of the relationship, and getting data from the table on the many side of the relationship, you need to use relatedtable.

if you are creating a column in the table on the many side of the relationship, and getting data from the table on the one side of the relationship, you need to use related.

( keep in mind, that relatedtable returns a table not a scalar value,  so in order to get data from using relatedtable, 

you need to then using an iterator over relatedtable . ) 

hope this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Victor_AguirreV]

This worked! I used the relatedtable as a variable as follows:

Is RF =

VAR RTable = RELATEDTABLE('All RFs')

VAR FilteredTable = FILTER(RTable, 'All RFs'[AcuityTecID]= 'All Trx'[Trans ID])

RETURN

    if(ISBLANK(

        IF (

        COUNTROWS(FilteredTable) > 0,

        MAXX(FilteredTable, 'All RFs'[AcuityTecID]),

        BLANK()

        )), "No","Yes"

    )

If I understand what this is doing correctly (I used gpt to help build it), the filtered table is returning a table, with the ones that match the ID in the all trx table.

Then the MAXX formula, is going line by line applying the logic, and then returning the max value?

I still don't have the hang of how iterative formulas work.

[Daniel29195]

@Victor_AguirreV 

Great job man . im glad that you were able to figure it out on your own. 👏