Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
We recently started using Power Bi and while doing some tests i've come across unexpected results when aggregating data between 2 tables with One to Many Relationship.
This is a part of our model:
When creating a matrix with count of id_pessoa by segmento_valor, i get strange values per row, and a total row count equal to the total of rows from table pessoa.
We only get the correct results when we build a matrix with count of person_id (scoring table) by segmento_valor, but we can't accept this solution, since we want to hide the tables keys from the users.
When creating a table view, with the fields id_pessoa and segmento_valor, i can confirm that the relationship is working (id_pessoa is duplicated for each row of the scoring table).
Can someone explain what are we doing wrong?
Thanks.
Solved! Go to Solution.
hi, @luisc
This is a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
For your case, when you count of id_pessoa (pessoa table) , It likes a measure as below:
Output = var _table=ADDCOLUMNS(scoring,"_id_pessod",RELATED(pessoa[id_pessoa])) return COUNTAX(_table,[_id_pessod])Result:
Regards,
Lin
hi, @luisc
This is a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
For your case, when you count of id_pessoa (pessoa table) , It likes a measure as below:
Output = var _table=ADDCOLUMNS(scoring,"_id_pessod",RELATED(pessoa[id_pessoa])) return COUNTAX(_table,[_id_pessod])Result:
Regards,
Lin
Thanks for the help and the posts.
Was really hoping that this was a problem with the model, since this is something that the users used to do in our other BI.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |