Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Maximum Mileage

Hi - hope you can help

I have two tables.

 

A fact table - Table 1

 



A dim table - Table 2

 


They are joined together using the Post Code Column.

I'm wanting to create a measure that will display as a new column in a table visual of the fact table. I want it to look like the below:

 


This column (measure) shows the maximum (highest) mileage for any given Booking Number based on the Post Codes associated with that booking. As an example booking number 689053 has three post codes associated with it - the one with the highest miles is SE at 70 miles - so in the maximum miles column it says 70 for all of the 689053 booking numbers.

Please could you help me with a measure as I'm stumped.

I hope this makes sense.

Kind regards

Chris

Need Help

10 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Anonymous 

     

    Try this mEasure

     

    Measure =
CALCULATE (
    MAX ( Dim[Miles] ),
    CROSSFILTER ( Dim[Post Code], 'Fact'[Post Code], BOTH ),
    CALCULATETABLE (
        VALUES ( 'Fact'[Post Code] ),
        ALLEXCEPT ( 'Fact', 'Fact'[Booking Number] )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Zubair

       

      Many thanks for your speedy response.  I have tried the measure and have got the below result:

       

       

      However this is not quite what I was after.  Please could you have another look?

       

      Thank you

       

      Chris

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Zubair

       

      Many thanks for your speedy response.  I have tried the measure and have got the below result:

       

       

      However this is not quite what I was after.  Please could you have another look?

       

      Thank you

       

      Chris

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        7 years ago

        HI Anonymous 

         

        Please try this MEASURE

        Could you share your file

         

        Measure = VAR myvalues= CALCULATETABLE (
        VALUES ( 'Fact'[Post Code] ),
        ALLEXCEPT ( 'Fact', 'Fact'[Booking Number] )
    )
RETURN
CALCULATE (
    MAX ( Dim[Miles] ),
    Filter(all(Dim),Dim[Post Code] in myvalues))
  • bogomda's avatar
    bogomda
    Resolver I
    7 years ago

    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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

       

       

       

       

      • bogomda's avatar
        bogomda
        Resolver I
        7 years ago

        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