Forum Discussion
Helper IVMeasure as a column for Bar chart Y axis selection
Dear all,
I have made a simple plot based on this view:
When I selelect "490" from the filter, the plot must show barchart with score "A". If selected "495" , then score "B" must be visible in the same plot. The Power BI has two tables,
a) the Score table:
| Week | A | B |
| 1 | 12 | 99 |
| 1 | 14 | 76 |
| 2 | 11 | 81 |
| 2 | 9 | 66 |
| 3 | 6 | 59 |
| 3 | 6 | 45 |
| 3 | 0 | 39 |
| 4 | 3 | 101 |
| 5 | 12 | 39 |
| 5 | 11 | 45 |
| 5 | 10 | 55 |
| 5 | 7 | 12 |
b) the Group table :
| Group | Week |
| 490 | 1 |
| 490 | 2 |
| 490 | 3 |
| 490 | 5 |
| 495 | 2 |
| 495 | 3 |
| 495 | 4 |
| 495 | 5 |
Column "Week" are connected:
I want to create Measure so that the right column will be picked up, depending wicht selection you did make from the filter, 490 = A and 495 = B.
I used this DAX:
HI CornelisV
The problem occurs because there is a many-to-many relationship between
'Group table'and'Score table'.
This creates ambiguity when trying to sum values in a bar chart Y-axis selection, which can lead to incorrect results.Solution:
To resolve this, you should introduce a bridge table that contains unique weeks, ensuring a proper relationship between the tables.
The model should be structured as follows:Then you can use DAX FORMULA
Sum_selected_group = VAR selected_group = SELECTEDVALUE('Group table'[Group]) VAR A = SUM('Score table'[A]) VAR B = SUM('Score table'[B]) RETURN IF (selected_group = 490, A, B)Result :
More information about handalling eith many to many relationships here :
https://www.youtube.com/watch?v=aPLaun-tTgQThe pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
4 Replies
- vojtechsima
Super User
hey, CornelisV ,
Remove the relationship, it's Many To Many, that will only produce duplicated results:
Add new column to your Group, that represent A/B:
write measure like this:_selected group = var _group = SELECTEDVALUE('Group'[columnToSum]) var _sum = IF( _group = "A", SUM(Scores[A]), SUM(Scores[B])) return _sum
CornelisVHi vojtechsima ,
Thank you for your support. This is also the solution, however the problem is that tables contains over 10,000 rows and 15 columns as result from SQL. The intention is to modify the table as less as possible, prerferably nothing. Nevertheless, thsi is a good learning point for me.
BR,
Cornelis
- Ritaf1983
HI CornelisV
The problem occurs because there is a many-to-many relationship between
'Group table'and'Score table'.
This creates ambiguity when trying to sum values in a bar chart Y-axis selection, which can lead to incorrect results.Solution:
To resolve this, you should introduce a bridge table that contains unique weeks, ensuring a proper relationship between the tables.
The model should be structured as follows:Then you can use DAX FORMULA
Sum_selected_group = VAR selected_group = SELECTEDVALUE('Group table'[Group]) VAR A = SUM('Score table'[A]) VAR B = SUM('Score table'[B]) RETURN IF (selected_group = 490, A, B)Result :
More information about handalling eith many to many relationships here :
https://www.youtube.com/watch?v=aPLaun-tTgQThe pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
