Forum Discussion
Helper IIIGetting SUM by other column relationship
Hi matkvaid
https://www.dropbox.com/t/028rB5MRFHkzjDsl
I understand the problem. Actually there is nothing in your data model that tells the Power Bi engine which hours spent by which driver on which car. No matter how your play with the relationships, as long as this information is missing there in no way to split the hours over cars. For example if the schedule table includes the car number then everything will work smoothly becuase it will become very clear to engine which car driven by the employer at a given date/time. (I applied the solution but it turned out that drivers can actually drive more than one car in a single day. Therefore it is not correct yet I will post it anyway)
We might be able to find a way out, for example if the driver can drive only one car in a specific date then it should be possible. We can create a cross-join table between fact and schedule in order to complete the schedule with car numbersComplete Schedule = SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Schedule, DISTINCT ('Fact' ) ), [Factual Time Of Departure] = [Data] && [Shipping Agent Employee Code] = [Darbuotojo Nr_] ), "FA_No_", [FA No_], "Shipping Agent Employee Code", [Shipping Agent Employee Code], "Valandu skaicius", [Valandu skaicius], "Date", [Data] )Then use this table instead in the model
Community ChampionHi matkvaid
https://www.dropbox.com/t/028rB5MRFHkzjDsl
I understand the problem. Actually there is nothing in your data model that tells the Power Bi engine which hours spent by which driver on which car. No matter how your play with the relationships, as long as this information is missing there in no way to split the hours over cars. For example if the schedule table includes the car number then everything will work smoothly becuase it will become very clear to engine which car driven by the employer at a given date/time. (I applied the solution but it turned out that drivers can actually drive more than one car in a single day. Therefore it is not correct yet I will post it anyway)
We might be able to find a way out, for example if the driver can drive only one car in a specific date then it should be possible. We can create a cross-join table between fact and schedule in order to complete the schedule with car numbers
Complete Schedule =
SELECTCOLUMNS (
FILTER (
CROSSJOIN ( Schedule, DISTINCT ('Fact' ) ),
[Factual Time Of Departure] = [Data]
&& [Shipping Agent Employee Code] = [Darbuotojo Nr_]
),
"FA_No_", [FA No_],
"Shipping Agent Employee Code", [Shipping Agent Employee Code],
"Valandu skaicius", [Valandu skaicius],
"Date", [Data]
)Then use this table instead in the model
Helper IIIHello again and thank You very much! That is fully enough of solution. There is very rare situation that drivers would change cars between days, normaly it is fully enough just to calculate one day worked hours like here with driven car. If there was two drivers this could be training, etc - so for now it is completely enough. I can have correct driven hours from GPS software, but this will not correlate with workschedule as this is full work day and cant be related to driven car in our database. So for my calculation this will work. Thank You again 🙂
- tamerj1
matkvaid
That's great!
It is also worth it to double cjeck your schedule data. I noticed the work hour are missing for at least one day (26 Mrch) which reflected a difference in the work days between my report and your original one. Hours and day must be computed out of the same table- matkvaid
I am cheking all data ant its structures, this is my first bigger model, it has few fact tables and i still learn to model data, because our structure has multiple tables for info, like in this example fact really has 'fact header' -> 'fact lines' and then all real sales values are per item in items value entry table... 🙂