DAX - recreate table for model

[Magpie_Rob]

Hello,
I'm trying to create a DAX bridge table for my model,

I have created the table using a union to get a 1:* relationship, in the bridge table I would like to get a field from the many. in the image below I'm expecting the top result where it returns the MAX number_of_accounts for the dupe_id. But I'm getting the lower result. where its bringing back the max for the table, rather than in context for the Id.  

I have also included a picture of the model. Qty Account I have added in as a Cal Column, the DAX for Qty Accounts is as below I have also tried MAXX. 

Qty Accounts = MAX('Multiple Account Customers'[number_of_accounts])

Thanks

Rob

[v-yanjiang-msft · Accepted solution]

Hi @Magpie_Rob ,

According to your description, you want to get the max of number_of_accounts for the value of Qty Accounts,the MAX('Multiple Account Customers'[number_of_accounts]) bring back the max of the table, you can use the CALCULATE for the max of the id. Here's my solution.

It's the details of CALCULATE DAX bellow.

https://docs.microsoft.com/en-us/dax/calculate-function-dax

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Magpie_Rob , What value add Dup Bridge table is doing. Because the model was 1-1 then you could have made that 1- M by force.

Can you share what is the need

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Magpie_Rob]

Hi @amitchandak, 
thanks for getting back to me .

So the 2 tables come from the same source but represent diffrent things. I'm looking for duplicate accounts where the ID's are diffrent but the combination of postcode and surname is the same. 

So the 1:1 relationship is just linking the unquie ID's and not the duplications. the dupe_id is the combination of postcode and surname but when drilling through, it filters to only matching rows where I need all rows with that postcode / surname id.

I'll stip out the sensive data and share shortly