utterly foxed by results of RELATED

[masplin]

I have extraordinarialy complex model I have inherited that has some fundamential structural errors which I am trying to resolve.   I have turned off every relationship apart from a few that are needed for this particualr calculation in case there was some odd feedback going on.

So I have a table ATEsActioned that has a field [ActionedbyAdviserID]  This has multipe rows on nultiple days for each AdviserID.  I have another table AdviserRole that contains a row for every day for that AdviserID and what their job and name was on that day.  The name is always the same for each adviserID, but job may change over time.   I have set the direction to single (Adviserrole filters ATEsActioned) as it is a many to many relationship

If I build a matrix with AdviserID from the first table and Name formt hesecond table it works fine as you would expect i.e. the AdviserID fitler s atable of AvdiserRole that has mant rows, but name is that same so doesn't blow up

However if i go on the ATEsActioned table and try to create a calcuated column with the name it errros in lots of cases. 

If i use this formula 

Name = IFERROR(CALCULATE(COUNTROWS(RELATEDTABLE(AdviserRole))),99999)

I get 40,461 for every row which is the total number of rows in the AdviserRole table.   I woudl have expected the RELATEDTABLE would only bring back the number of rows where the AdviserID match which is about 150 for each adviser. 

My original code was this which i thought was essentially the same as building the visual

Name2 = IFERROR(CALCULATE(VALUES(AdviserRole[Name]),RELATEDTABLE(AdviserRole)),"Error")

I must be doing something extremely stupid as never had an issue with extracting data using RELATED or RELATEDTABLE.  I have created a mini workbook which displays the same issue. Is there something blindingly obvious I'm doing wrong with my code?

Test Data 

Thanks for any advice

Mike

[mahoneypat · Accepted solution]

Does it work if you just use the following? (update with correct table/column names)

Name = CALCULATE(COUNTROWS(AdviserRole)

and if the relationship filters the wrong direction

Name = CALCULATE(COUNTROWS(AdviserRole), CROSSFILTER(Table1[RelationshipColumn], AdviserRole[RelationshipColumn], BOTH))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[masplin]

Hi pat

First verison at least produces a differnet number for each adviserID, but still way too high

Second version is 

Name = CALCULATE(VALUES(Adviserrole[Name]), CROSSFILTER(Actioned[ActionedbyAdviserID], AdviserRole[AdviserId], BOTH)) 

This works on my little mini workbook, but falls over on the real one  if i turn on all the other relationships as getting an ambiguous path error. 

As I understand it the CROSSFILTER set to both is the same as setting the manay-to-many rleationship to BOTH but jsut within the measure?  If I set the relationship to BOTH in the manage relationships module then 

Name = CALCULATE(VALUES(AdviserRole[Name])) also works in my mini model.  i.e. you dont need to use RELATEDTABLE to get the result. If I try this in my main model I get an error as have another table related to the AdviserRole table. 

What I am trying to understand is was it unreasonable for me to expect the RELATED/RELATEDTABLE functions to operate as I expected when you use a Many-to-Many relationship?   In a Many-to-1 relationship RELATEDTABLE finds all the rows in the many side and VALUES pridces an answer if all rows have the same value.  Maube by setting the many-to-many as Single(AdviserRole filters ATEsActioned) you cant produce any filtering in the opposite direction???

I'm going to have to go back to the drawing board and see if i can rebuild this using bridge tables instead of many-to-many. Frustrating as fundamentally think all my visualls are calculating correctly!!!

Thanks

Mike

[Greg_Deckler]

@masplin - This generally occurs when you do not have a relationship between tables or your relationship is set to Single and you are trying to calculate something in Table B and the relationship direction is such that you are not filtering Table A.

[masplin]

Hi greg.   If you look at the mini modle i attahce dunder Test Data you can see the setup.  I do have a rleationship but because it is many to many i have set it as single.  The relationship must be working or the visual would not pick up the right names??? 

Maybe thisis some peculiarity of many to many rtlrationships that the RELATEDTABLE function doesn't operate in the same way as going ofrm a one to many table pciking out values?

[Greg_Deckler]

@masplin - Many-to-Many relationships are bad and can cause all kinds of problems. I would highly recommend putting a bridge table in between them and use that bridge table in your visualizations/calculations.

[masplin]

i just spent 8 hours building in all these many to many tables and working fine on the visuals!!!!! All of them are set to single as know you can get bizzare behaviour with "both".  The problem i was solving was caused by many-to-one rleationships that weren't capturing the complexities of the data.   I have no idea how to design bridge tables andachieve the same thing.

It just doesn't make sense the you can't use RELATEDTABLE functionlaity. I'll get googling.

Thanks

[Greg_Deckler]

@masplin - So, the easiest way to get a bridge table is to do this:

Bridge Table = 

  DISTINCT(

    UNION(

      SELECTCOLUMNS('Table1',"Column",[Column]),

      SELECTCOLUMNS('Table2',"Column",[Column])

    )

  )

Table1 *<>1 Bridge 1<>*Table2

That's a many-to-many built with a bridge table. Using the bridge table in your visuals and such keeps things orderly and working well. This was how we did things prior to many-to-many being in Power BI. But, I've never been able to get consistent results with native many-to-many and so I tend to avoid them at all costs and still use bridge tables when I need them. I have modeled extremely complex data models using bridge tables such as patient health care and treatment data, which is actually like 5 bridge tables. Think patients with multiple visists with multiple diagnoses with multiple prescriptions, etc. etc. etc.

[masplin]

Hi greg

I think I've got my head round this but could you sanity check what I'm planning?

My key table has these fields where there is a row for every date that each adviser worked on a client so there can be multiple rows on any date/adviser combination

Client

Date

AdviserID

My bridge tables are

Client - list of unique clients

Date - standard date table

AdviserID - list of unique IDs

i have a dozen fact tables all of which contain

Client

Date

AdviserID

So do I create these relationships

Keytable[client]<>Client[client]->Fact[Client]

Keytable[date]<>date[date]->Fact[date]

Keytable[AdviserID]<>AdviserID[AdviserID]->Fact[AdviserID]

I use my bridge tables as my slicers and inuts on my visuals

I have a 2 way relationship to my Keytable on all my bridges but one way to my facts

So if I chose say 4 clients this filters KeyTable to those clients. Then filter a date range but say only 3 clients have entries for those dates this filters back to the client table so only those 3 clients are filters on the fact table. 

Have I understood this correctly? So as i pick from my slicers the choices in the other slicers will reduce .

Appreciate any advice before I dive in

Mike

[masplin]

My brain is to fried to do this today, but i'll start again tomorow with your idea.  Wish I'd know that before I started!!!!  

I'm guessing that Many-to-many is a very differnet beast under the bonnet so the elegant related/related table functions just don't work.

Thnaks for your help