locked
DAX - SUM of MAX with a Twist RRS feed

  • Question

  • Hi Experts!

    I have a problem where I need to calculate the correct odometer reading value for vehicles. I need to take the highest reading of each vehicle and sum these numbers up to a total.

    I have Vehicles, and each vehicle may have multiple contracts (when a the contract of the vehicle expires a new contract might be created), and then I have the Odometer readings with in turn have a Reading Date:

    Model 

    I have defined the Measure "Distance Travelled"  like this

    Distance Travelled:=  SUMX(values('Vehicle'[VehicleKey]),CALCULATE( MAX([Odometer Reading])))

    BUT Unfortunately this measure only works as long as the user does not use the Date dimension. When the date dimension is used, everything freeze until I get a message that the RAM has ran out, even if I filter on a single Vehicle.

    However, if I filter on both a vehicle and also on the 3 or 4 contracts that belong to this vehicle I get the expected result. Seems to be that my formula is considering all contracts and not only the few contracts that should be implied by the currently selected Vehicle.

    Any Ideas how to make a measure work?

    (I need to keep the model snow-flaked like this because it is a part of a bigger model that cannot be changed)

    Wednesday, September 20, 2017 8:25 AM

Answers

  • Hi

    Yes you are correct it was not a real model. I created a mockup model to illustrate the problem since my real problem existed in a bigger model in SSAS Tabular. Apparently One-to-many relationships are drawn differently in SSAS Tabular and Power Pivot so I made an error.

    Anyway, I created a real Pivot Pivot Model with test data with the intent to upload it here. When I was finished I was very surprised to see that the Power Pivot Model did not suffer from my original problem, it worked perfectly.

    When I compared the Power Pivot to my real Tabular model I discovered that I had originally written the measure as

    Distance Traveled:=  SUMX(values('Contract'[VehicleKey]),CALCULATE( MAX([Odometer Reading])))

    instead of

    Distance Traveled:=  SUMX(values('Vehicle'[VehicleKey]),CALCULATE( MAX([Odometer Reading])))

    I changed this and it made all the difference, lucky me =)


    Thursday, September 21, 2017 12:42 PM

All replies

  • Hi,

    Can you share the link from where I can download your workbook.  Please also show the expected result there.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Thursday, September 21, 2017 12:08 AM
  • Hi, thanks for for helping out!

    My data it a company secret but I will create similar data and get back to you.

    /henrik

    Thursday, September 21, 2017 7:19 AM
  • Is this a picture of your real model? Because based on your description that a Vehicle can have 1 or more contracts and a contract can have one or more readings - then all your relationships are defined in the wrong direction.

    You diagram has a model where you can have multiple contract records per reading and multiple vehicles per contract.

    And the relationship to date says that you can only have one reading for a given date which does not make any sense.


    http://darren.gosbell.com - please mark correct answers

    Thursday, September 21, 2017 9:50 AM
  • Hi

    Yes you are correct it was not a real model. I created a mockup model to illustrate the problem since my real problem existed in a bigger model in SSAS Tabular. Apparently One-to-many relationships are drawn differently in SSAS Tabular and Power Pivot so I made an error.

    Anyway, I created a real Pivot Pivot Model with test data with the intent to upload it here. When I was finished I was very surprised to see that the Power Pivot Model did not suffer from my original problem, it worked perfectly.

    When I compared the Power Pivot to my real Tabular model I discovered that I had originally written the measure as

    Distance Traveled:=  SUMX(values('Contract'[VehicleKey]),CALCULATE( MAX([Odometer Reading])))

    instead of

    Distance Traveled:=  SUMX(values('Vehicle'[VehicleKey]),CALCULATE( MAX([Odometer Reading])))

    I changed this and it made all the difference, lucky me =)


    Thursday, September 21, 2017 12:42 PM
  • Hi Henrik,

    Thanks for your question.

    I am glad to know that you have resolved this issue by yourself. Please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, September 22, 2017 1:24 AM