Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
donnynz
Helper II
Helper II

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 

1 ACCEPTED SOLUTION

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

View solution in original post

42 REPLIES 42

I've just re-run using your new dataset and I don't get the same results as you. In fact, I get results which match your expected results in all cases apart from the 11 rows for which you state that the Minimum weight should be blank. There are 20 other rows in the data you sent for which you are claiming that the result of my formula and your expected results differ, but when I run the report that's not the case; I get your expected results.

Regards

OK that is confusing. What code are you using? If you are happy to share i'll make sure i have the same and re-run.

As always, still the same one that I posted way back in post #11.

Regards

When I use this code I get all 0 as the result for every runner (which is the code from post 11) I added it to the Race Data#1 table:

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
)
 
If I change this part of the code to the following I get results come through but as previously shared some races are correct and some incorrect:
ALLEXCEPT ( 'Race Data#1', 'Neurals Mstr'[Race No.] ),
'Neurals Mstr'[Race No.] = RaceNo
)
 
Not sure why it works for you but not me. Unless you can see something obviously wrong we might be at a deadend. Thank you so much for trying to help. This was the first time i've posted a question and learnt a little bit with just what i need to supply to get help.
 
Thanks 

Agreed, the only way we could resolve this now would be via sharing the actual file somehow, though I understand if you're not willing to do that.

All the best and hope you manage to resolve it.

Regards

Hi, I'm happy to share. Just how to do that. Am I allowed to give my email on this and then you can email me and then I can forward you the file?

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:

 

donnynz_0-1635140780940.png

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

 

Again thanks for any help. 

 

Jos_Woolley
Solution Sage
Solution Sage

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?

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
Solution Sage
Solution Sage

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.

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
Solution Sage
Solution Sage

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

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

 

donnynz_0-1635065492469.png

 

Jos_Woolley
Solution Sage
Solution Sage

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.

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
Solution Sage
Solution Sage

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

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
Solution Sage
Solution Sage

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
        )

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.]
)
donnynz
Helper II
Helper II

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
Solution Sage
Solution Sage

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors