cancel
Showing results for
Did you mean:
Frequent Visitor

## Measure at many to one relationship not working

Hi,

I have an issue when using Measures, to simplify it I have made the following example. I have the following 2 tables:

Table "RACES"

 RACE VEHICLE DISTANCE (km) TIME (h) 1 ALPHA 10 0,2 2 ALPHA 20 0,5 3 BRAVO 15 0,5 4 BRAVO 30 0,8 5 BRAVO 25 0,7 6 CHARLIE 25 0,6 7 CHARLIE 50 1,3

Table "DRIVERS";

 RACE VEHICLE DRIVERS 1 ALPHA JOHN 1 ALPHA MIKE 2 ALPHA JOHN 2 ALPHA PETER 3 BRAVO TOM 3 BRAVO PETER 4 BRAVO MIKE 4 BRAVO PETER 5 BRAVO JOHN 5 BRAVO MIKE 6 CHARLIE MIKE 7 CHARLIE PETER 7 CHARLIE TOM

In table RACES, I have 7 different races with a distance and time for each one, these 7 races are performed by 3 different cars. Each of these cars can be driven by 1 or 2 drivers, this information is in table "DRIVERS". Both tables are linked by the field RACE.

In PBI I have made a Measure in table RACES called "SPEED" = sum(RACES[DISTANCE (km)])/sum(RACES[TIME (h)]). This Measure calculates correctly when displayed at Race level:

The problem is when I want to see the SPEED by DRIVER (I intend to see average speed of all races in which the driver has participated):

As you can see, the measure is not properly displayed. Another thing I want to do is to filter by DRIVER and see all the races in which the specified driver has participated with its SPEEDS. This doesn't work either:

Any solution on that? I could share the sample .pbix file but I don't know how to do this in this forum XD, let me know if you need it.

Thanks you all!!

Manu

1 ACCEPTED SOLUTION
Responsive Resident
Speed = CALCULATE(SUM(RACES[DISTANCE (km)])/SUM(RACES[TIME (h)]),DRIVERS)
4 REPLIES 4
Responsive Resident
Speed = CALCULATE(SUM(RACES[DISTANCE (km)])/SUM(RACES[TIME (h)]),DRIVERS)
Frequent Visitor

That worked! Thanks!!

Resolver V

Hi Manu,

Since you're not storing the distance and time at driver level, you won't be able to figure them out. These metrics are for the races, hence why they would work at this level. If you were to store the time at driver level (eg. each drivers total time), you'd be able to use that then look against the race distance to get their speed, as well as the average speed per race.

Hope that helps!

Frequent Visitor

Ok, I understand that. But can you offer any solution? For example, i know that JOHN has participated in Races 1, 2 and 5, and I know the SPEED of each one of this races, there is no way to filter by DRIVER = JOHN and see these 3 races with their SPEEDS?

Manu

Announcements

#### Launching new user group features

Learn how to create your own user groups today!