Measure from one to many relationship causing exponential table growth

[Anonymous]

I have had this a few times, and I think I am missing something regarding relationships or Measure behavior.

Let's start with this relationship.  The 'vdr Info' table has only one record per "VDR" which gives me a Table that shows only one VDR per row.  Good up to this point.

Now I add a "responsibility" table that also joins one-to many with the id_division field.

This table has something like this...

So what I want is to display the "resp_value" field in my Table.  It has to show the item that matches the id_division AND a specific bi_category.  So what I did is create a measure like this...

respVCodeErr = CALCULATE(
	FIRSTNONBLANK('IC eng_responsibility_list'[resp_value], TRUE())
    ,FILTER('IC eng_responsibility_list',[bi_category] = "Critical vCodes")
)

When I add this measure to the table, my number of records goes up exponentially to show me all permutations of column values.  Instead of 1 row per VDR, each VDR now has lots of rows, all with incorrect information...

Removing the Measure from the table sets everything right again.  Can anyone shed any insight?  Thanks!

[v-piga-msft]

Hi @Anonymous,

For your case, it seems that you have many tables and multiple relationships. I need more details.

If it is convenient, please share a dummy pbix file which can reproduce the scenario and your desired output, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best  Regards,

Cherry