Forum Discussion
Change aggregation from default on numeric column
Hi Anonymous
Based on your information I looked into the model again ... to find out that you are right (yeah right 😁 ), but only for the model shown above.
Now after some more testing, I would dare to say that there are 2 possible solutions:
- either keep the model as is above, and use
rooster.hours.room = sumx(reservations, reservations[res_info2] * COUNTROWS(RELATEDTABLE(roomReservations)))
- or adapt the model and set the 'room-roomReservations' relation to filter in both directions as well. In that case you can also use the formula below, since filtering will cascade from reservations over roomReservations into rooms. So the RELATEDTABLE function isn't limited to follow links to 'the next' table, but to the 'chain of related, but filtered tables':
rooster.hours.room = sumx(reservations, reservations[res_info2] * COUNTROWS(RELATEDTABLE(rooms)))
So the first formula works in both case, due to the 'reservation-rooomReservation' filtering in both directions anyhow.
Wether the one datamodel is better than the other: depends on how you want your model/measures/filters to work in your particular situation. The one is not bettter than the other.
If in doubt: I learned that the best way to define that: make a mini model with very limited but 'situation-representative' data and try.
Hope this helps someone else
C.
I found the solution:
Hi cdebackere
I'm glad you found the solution on your own. You can mark your reply as a solution to help other users with similar problems find the answer quickly.
As for why you need roomReservations instead of rooms, here is my understanding:
In DAX, the RELATEDTABLE function returns the table associated with the current row. Since roomReservations is the intermediate table between reservations and rooms, it contains the relationship between each reservation and room. Therefore, when you use RELATEDTABLE(roomReservations), you are actually counting the number of rooms associated with each reservation.
If you use the rooms table, DAX has no direct way of knowing which rooms are associated with each reservation because the rooms table itself doesn't contain these relationships. roomReservations, as an intermediate table, provides information about such relationships, making COUNTROWS(RELATEDTABLE(roomReservations)) to correctly calculate the number of rooms per reservation.
I hope this explanation helps you better understand why you need to use the roomReservations table.
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous
Based on your information I looked into the model again ... to find out that you are right (yeah right 😁 ), but only for the model shown above.
Now after some more testing, I would dare to say that there are 2 possible solutions:
- either keep the model as is above, and use
rooster.hours.room = sumx(reservations, reservations[res_info2] * COUNTROWS(RELATEDTABLE(roomReservations)))
- or adapt the model and set the 'room-roomReservations' relation to filter in both directions as well. In that case you can also use the formula below, since filtering will cascade from reservations over roomReservations into rooms. So the RELATEDTABLE function isn't limited to follow links to 'the next' table, but to the 'chain of related, but filtered tables':
rooster.hours.room = sumx(reservations, reservations[res_info2] * COUNTROWS(RELATEDTABLE(rooms)))
So the first formula works in both case, due to the 'reservation-rooomReservation' filtering in both directions anyhow.
Wether the one datamodel is better than the other: depends on how you want your model/measures/filters to work in your particular situation. The one is not bettter than the other.
If in doubt: I learned that the best way to define that: make a mini model with very limited but 'situation-representative' data and try.
Hope this helps someone else
C.