Forum Discussion
Maximum Mileage
see a link to pbix file with example. it shows two solutions:
1) calculated column (witn no summarized values option chosen)
2) a measure solution (just drop it to matrix and it should work)
Chris,
do following:
1. create new calculated column "Maximum Miles" in table1
2. paste following formula and you will get desired results
In your case it make sense to create a calculated column (not a measure) because you need to update your column upon addition of new records.
= VAR CurrentBooking = Table1[Booking Number] RETURN MAXX( FILTER(Table1,Table1[Booking Number]=CurrentBooking), RELATED(Table2[Miles]) )
this is the link to a sample solution - https://1drv.ms/x/s!AhUWZ84uo7UAglc8kvkwm1THdmsj
Hi Zubair_Muhammad & bogomda
Thank you both for your quick and detailed responses.
@Zubair_Muhammad - Thanks for your efforts but unfortunately I couldn’t get them to give the desired result.
bogomda - This works really well.
BUT!! – apologies – when I simplified the test data I over simplified and missed out a key element.
Blow is a screen shot from the pbix file. I think you’ll instantly see my problem.
There is a column called ‘Drop Number’ – each Booking Number and Postcode can have multiple lines in the Fact table as there are potentially multiple drop numbers.
Although in this instance I’m not interested in this column it is causing an issue when it feeds through into the ‘desired result’ as it’s summing up the ‘MaxMiles’ row by row.
Really sorry I missed this out. Hopefully it’s just a tweak of the existing formula?
Thanks again both – much appreciated.
Chris
Visual by defaul sums numerical values. You need to left click on Maximum Miles value under Values section and choose option "Don't Summarize" to get desired result
see a link to pbix file with example. it shows two solutions:
1) calculated column (witn no summarized values option chosen)
2) a measure solution (just drop it to matrix and it should work)
Thanks very much, that works perfectly :D
Chris