cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manutejedor
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"

 

RACEVEHICLEDISTANCE (km)TIME (h)
1ALPHA100,2
2ALPHA200,5
3BRAVO150,5
4BRAVO300,8
5BRAVO250,7
6CHARLIE250,6
7CHARLIE501,3

 

Table "DRIVERS";

 

RACEVEHICLEDRIVERS
1ALPHAJOHN
1ALPHAMIKE
2ALPHAJOHN
2ALPHAPETER
3BRAVOTOM
3BRAVOPETER
4BRAVOMIKE
4BRAVOPETER
5BRAVOJOHN
5BRAVOMIKE
6CHARLIEMIKE
7CHARLIEPETER
7CHARLIETOM

 

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:

 

Screenshot - 18_05_2018 , 11_15_14.jpg 

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):

 

driver.jpg

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:

 

filter.jpg

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
iamprajot
Responsive Resident
Responsive Resident

Speed = CALCULATE(SUM(RACES[DISTANCE (km)])/SUM(RACES[TIME (h)]),DRIVERS)

View solution in original post

4 REPLIES 4
iamprajot
Responsive Resident
Responsive Resident

Speed = CALCULATE(SUM(RACES[DISTANCE (km)])/SUM(RACES[TIME (h)]),DRIVERS)

View solution in original post

That worked! Thanks!!

JoHo_BI
Resolver V
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!

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.