Forum Discussion
DAX - recreate table for model
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.
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
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.
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
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
