Change aggregation from default on numeric column

[cdebackere]

I have a data model with a fact and 2 dimentions, each linked over a factless fact table, since the relations are n-m

I now go further on 1 dimention, let's say room. Here is a mini dataset example for the reservations, roomReservations and rooms:

 

I create following visual to report the total of res_info2 (eg duration of reservation), which is fine at room level. I added reservation ID as legend so you can easily see where it comes from

But now, I zoom out ou the x-axis, to the level of A and B. And there the default aggregation is not what I want:

For 'A', the total number of booked reservations is 6 for room_1 and 5 for room_2. So the total accupation of 'A' I want is 11. But since reservation ID 1 is for 2 rooms in 'A', The default aggregation only count the reservation once at 'A' level. And I only get 9 instead of the 11 I want to see.

And the same again when I zoom out 1 more level, due to reservation ID 3 which apears in both A and B. I need 11(not 9) + 8 = 19, but get 16:

I uderstand the reasoning for this default behaviour.

But how can I change that?

I tried added a measure to all 3 tables with code like this: 

rooster.hours = sum(reservations[res_info2])

rooster.hours.room = sumx(reservations, reservations[res_info2] * COUNTROWS(RELATEDTABLE(rooms)))

but no change with rooster.hours or not the wanted change with rooster.hours.room:

I get the impression it calculates the related rows directly for the room dimention since for 'A' which has 2 rooms, all reservations get multiplied by 2, rather than getting multiplied by the number of room linked to the reservation (withing the filter), which would be 1 or 2 depending on reservation.

I feel confined this can be set up powerbi ... Right? and how?

Thx,

Christof

[cdebackere]

I found the solution:

rooster.hours.room = sumx(reservations, reservations[res_info2] * COUNTROWS(RELATEDTABLE(roomReservations)))

I found it by trial and error. I cannot really explain why it needs to be roomReservations and not rooms

If anyone can explain so I better understand for the future, please do 🙂

Chhers,

Christof

[Anonymous]

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.

[cdebackere · Accepted solution]

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.