Forum Discussion

donnynz's avatar
donnynz
Helper II
4 years ago
Solved

Dax code to calculate a value above the minimum value

Hello, this is my first time posting so hopefully I provide the right information to get help on an area I need to improve, and that's DAX coding.

 

I have a report based on horse racing and one measure I want to include is for each race and for each horse how much weight is it carrying above the minimum. So for example Horse A carrying weight is 57.5, Horse B 56.0, Horse C 55.0 and Horse D is 52.5. I want a column that will show the weight being carried above the minimum weight for the selected race. So based on the above it should show Horse A 5.0, Horse B 3.5, Horse C 2.5 and Horse D 0 (as this is the lowest weighted horse). 

 

I've tried a few things around DAX code but get the same weight the horse already shows as carrying or all show 0, very frustrating.

 

Any help is much appreciated.

 

Thanks

 

Brendan 

  • donnynz's avatar
    donnynz
    4 years ago

    I'm trying to add a new measure to the table 'Race Data#1' but get the following message:

     

    The column 'Neurals Mstr[Race No.]' either doesn't exist or doesn't have a relationship to any table available in the current context.

     

    The two tables 'Race Data#1' and 'Neurals Mstr' have a relationship based on horse name as that is common in both. I also have date as common in both tables. I'm wondering if that is what is causing the error message as the DAX is about Race No. and Carry Weight which are not used as the relationship connector?

     

    Thanks

42 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    4 years ago

    Hi,

    Where is the minimum weight for the race stored? I'm struggling to see why this wouldn't just be a simple subtraction between two columns. Perhaps post a small dataset together with expected results.

    Regards

    • donnynz's avatar
      donnynz
      Helper II
      4 years ago

      They are in the same column. I'll do a sample set and will attach soon

       

      Thanks

  • donnynz's avatar
    donnynz
    Helper II
    4 years ago
    Horse No.HorseWeightWeight above Minimum (desired output)Race No.CourseDate
    1HE RUNS AWAY5951Doomben23-Oct-21
    2HONORABLE SPIRIT5951Doomben23-Oct-21
    3IRISH SEQUEL5841Doomben23-Oct-21
    4ACHIEVER57.53.51Doomben23-Oct-21
    5CHOCOLATIER5401Doomben23-Oct-21
    6NEMINGAH5401Doomben23-Oct-21
    7MONEY SHOT5401Doomben23-Oct-21
    1MISHANI MENTAL5722Doomben23-Oct-21
    2PRIMED FOR VICTORY5722Doomben23-Oct-21
    3EXO LADY5502Doomben23-Oct-21
    4HONKYTONK DIVA5502Doomben23-Oct-21
    5SPANISH ANGELS5502Doomben23-Oct-21
    6FLORAL OASIS5502Doomben23-Oct-21
    7SHOTGUN DIVA5502Doomben23-Oct-21
    8OLYMPIC MOTTO5502Doomben23-Oct-21
    1ALREADY BLESSED6043Doomben23-Oct-21
    3ZINGALONG6043Doomben23-Oct-21
    4FROZEN IN TIME59.53.53Doomben23-Oct-21
    5MORT DOYLE5933Doomben23-Oct-21
    6VALIANT5933Doomben23-Oct-21
    7BLUE ODYSSEY58.52.53Doomben23-Oct-21
    11SHAMATON57.51.53Doomben23-Oct-21
    12WINSIDE5713Doomben23-Oct-21
    13ASHZAYA5603Doomben23-Oct-21
  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    4 years ago

    Thanks.

    Weight above Minimum =
VAR RaceNo = 'Table'[Race No.]
RETURN
    'Table'[Weight]
        - CALCULATE (
            MIN ( 'Table'[Weight] ),
            ALLEXCEPT ( 'Table', 'Table'[Race No.] ),
            'Table'[Race No.] = RaceNo
        )
    • donnynz's avatar
      donnynz
      Helper II
      4 years ago

      Hi, thanks for the help. I think I'm doing something wrong as getting a message "Cannot find table 'Table'." 

      The Race No. is in a dataset called 'Neurals Mstr' and the Carry Weight is in a dataset called 'Race Data#1". I'm confused cause further down the code you supplied the dataset and name seems to be fine but an issue in those first few lines? 

       

      Weight above Minimum =
      VAR RaceNo. = 'Neurals Mstr'[Race No.]
      RETURN
      'Race Data#1'[Carry Weight]
      - CALCULATE (
      MIN ( 'Race Data#1'[Carry Weight] ),
      ALLEXCEPT ( 'Race Data#1', 'Neurals Mstr'[Race No.] ),
      'Neurals Mstr'[Race No.]
      )
  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    4 years ago

    My calculated column was based on the table data you provided. I assumed a name of 'Table' for that table, which obviously you should amend as required.

    Regards

    • donnynz's avatar
      donnynz
      Helper II
      4 years ago

      Hi Jos,

       

      Yes appreciate didn't tell you that and I had to amend the tables to match what I have. But for some reason this part of the code:

      VAR RaceNo. = 'Neurals Mstr'[Race No.]
      RETURN
      'Race Data#1'[Carry Weight]
       
      There is red squiggle lines under the names Race No. and Carry Weight which is so confusing cause that is the names and when entered further done the code it seems to have accepted.
       
      Thanks again for your help.
  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    4 years ago

    Thanks, Brendan, but I'm a bit confused now. You posted a single table as your dataset, but from your last couple of posts I'm thinking that in reality that might not be the case, correct? You in fact have two tables? Is there a relationship between these two tables? Perhaps you should post some more realistic data which illustrates your actual set-up.

    • donnynz's avatar
      donnynz
      Helper II
      4 years ago

      Hi,

       

      Sorry first time posting for help so learning lol. There is a relationship between the two datasets via the measure name 'Horse'

       

      Table name 'Neurals Mstr'

      Horse No.HorseRace No.CourseDate
      1HE RUNS AWAY1Doomben23-Oct-21
      3IRISH SEQUEL1Doomben23-Oct-21
      4ACHIEVER1Doomben23-Oct-21
      5CHOCOLATIER1Doomben23-Oct-21
      6NEMINGAH1Doomben23-Oct-21
      7MONEY SHOT1Doomben23-Oct-21
      1MISHANI MENTAL2Doomben23-Oct-21
      2PRIMED FOR VICTORY2Doomben23-Oct-21
      3EXO LADY2Doomben23-Oct-21
      4HONKYTONK DIVA2Doomben23-Oct-21
      5SPANISH ANGELS2Doomben23-Oct-21
      6FLORAL OASIS2Doomben23-Oct-21
      7SHOTGUN DIVA2Doomben23-Oct-21
      8OLYMPIC MOTTO2Doomben23-Oct-21
      1ALREADY BLESSED3Doomben23-Oct-21
      4FROZEN IN TIME3Doomben23-Oct-21
      5MORT DOYLE3Doomben23-Oct-21
      6VALIANT3Doomben23-Oct-21
      7BLUE ODYSSEY3Doomben23-Oct-21
      8LYRICAL GIRL3Doomben23-Oct-21
      9PLATINUM EUROS3Doomben23-Oct-21
      10SOUBRETTE3Doomben23-Oct-21
      11SHAMATON3Doomben23-Oct-21
      12WINSIDE3Doomben23-Oct-21
      13ASHZAYA3Doomben23-Oct-21

       

      Table named 'Race Data#1'

      Horse No.HorseCarry WeightWeight above Minimum (desired output)
      1HE RUNS AWAY595
      3IRISH SEQUEL584
      4ACHIEVER57.53.5
      5CHOCOLATIER540
      6NEMINGAH540
      7MONEY SHOT540
      1MISHANI MENTAL572
      2PRIMED FOR VICTORY572
      3EXO LADY550
      4HONKYTONK DIVA550
      5SPANISH ANGELS550
      6FLORAL OASIS550
      7SHOTGUN DIVA550
      8OLYMPIC MOTTO550
      1ALREADY BLESSED604
      4FROZEN IN TIME59.53.5
      5MORT DOYLE593
      6VALIANT593
      7BLUE ODYSSEY58.52.5
      8LYRICAL GIRL58.52.5
      9PLATINUM EUROS58.52.5
      10SOUBRETTE582
      11SHAMATON57.51.5
      12WINSIDE571
      13ASHZAYA560

       

      Hope that is more useful?

       

      Thanks

       

       

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    4 years ago

    Perfect, thanks. Try:

    Weight above Minimum =
VAR RaceNo =
    RELATED ( 'Neurals Mstr'[Race No.] )
RETURN
    'Race Data#1'[Carry Weight]
        - CALCULATE (
            MIN ( 'Race Data#1'[Carry Weight] ),
            ALLEXCEPT ( 'Neurals Mstr', 'Neurals Mstr'[Race No.] ),
            'Neurals Mstr'[Race No.] = RaceNo
        )

    Regards

    • donnynz's avatar
      donnynz
      Helper II
      4 years ago

      Hi, It almost looks there, but just an issue with the top part which i find so confusing cause the names looked correct?

       

       

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    4 years ago

    Double-check that the relationship exists between the tables and that the column names match precisely as I gave them, no additional spaces at the end, etc. You can also simply delete the flagged column references in the formula and rewrite them - the intellisense should pop up to let you choose the desired columns.

    • donnynz's avatar
      donnynz
      Helper II
      4 years ago

      Hi Jos,

       

      I just can't figure it out, the name looks the same and if I try and rewrite them the intellisense for some reason doesn't appear. I have a relationship between those two tables being the horse name and the columns being used are Race No. and Carry Weight, so unless that is a reason for it not to work.

       

      Anyway thank you for your assistance and for trying to send me some code, I do appreciate your time.

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    4 years ago

    Wait a sec, you have put that column in the correct table, haven't you? That is, as per your desired results, in the Race Data#1 table, and not in the Neurals Mstr table?

    • donnynz's avatar
      donnynz
      Helper II
      4 years ago

      I'm trying to add a new measure to the table 'Race Data#1' but get the following message:

       

      The column 'Neurals Mstr[Race No.]' either doesn't exist or doesn't have a relationship to any table available in the current context.

       

      The two tables 'Race Data#1' and 'Neurals Mstr' have a relationship based on horse name as that is common in both. I also have date as common in both tables. I'm wondering if that is what is causing the error message as the DAX is about Race No. and Carry Weight which are not used as the relationship connector?

       

      Thanks

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    4 years ago

    Ok, for starters this isn't a measure, it's a calculated column. And the error you're getting suggests to me that you're attempting to put the calculated column I provided in the wrong table. I suggest you go back to post #10 in this thread and check very carefully the information you posted, especially the table names.

    Regards

    • donnynz's avatar
      donnynz
      Helper II
      4 years ago

      Sorry Jos, I think I've got confused and tried to add as a measure instead of adding a custom column. Should I be adding as a customer column? I am trying to add as a column in the Race Data#1 table, when I do I get the following:

       

      I'm getting the red squiggle at the start of the code line.

       

      Again thanks for any help. 

       

    • donnynz's avatar
      donnynz
      Helper II
      4 years ago

      Hi Jos,

       

      Sorry to bother you but had an update. The first table after ALLEXECPT was 'neural mstr' but changed it to 'All Data#1'. Doing this (as per below) now produces data.

       

      Weight above Minimum =
      VAR RaceNo =
      RELATED ( 'Neurals Mstr'[Race No.] )
      RETURN
      'Race Data#1'[Carry Weight]
      - CALCULATE (
      MIN ( 'Race Data#1'[Carry Weight] ),
      ALLEXCEPT ( 'Race Data#1', 'Neurals Mstr'[Race No.] ),
      'Neurals Mstr'[Race No.] = RaceNo
      )
       
      The only issue is some races it returns the weight above minimum as expected but for races it is wrong.
       
      RIGHT

      WRONG

      Do you have any idea why the second table shows the minimum weight incorrectly?

       

      Thanks

      • Jos_Woolley's avatar
        Jos_Woolley
        Solution Sage
        4 years ago

        I'm afraid I wouldn't be able to tell you unless I saw the precise data from the two tables as well as the exact version of the formula you are using. If the real table data is too big to give here, then you'll have to create reduced versions which exhibit the issue you're having. 

        Regards

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    4 years ago

    Don't add the column within Power Query! You should be adding that column to your table within DAX.

    Cheers

    • donnynz's avatar
      donnynz
      Helper II
      4 years ago

      Hi Jos, I finally got it!!! I added the column BUT it returns all 0s in the column for weight above the minimum.