topic Re: Dax code to calculate a value above the minimum value in DAX Commands and Tips

Dax code to calculate a value above the minimum value

donnynz — Sun, 24 Oct 2021 05:14:42 GMT

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

Re: Dax code to calculate a value above the minimum value

Jos_Woolley — Sun, 24 Oct 2021 05:25:27 GMT

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

Re: Dax code to calculate a value above the minimum value

donnynz — Sun, 24 Oct 2021 05:39:52 GMT

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

Thanks

Re: Dax code to calculate a value above the minimum value

donnynz — Sun, 24 Oct 2021 05:57:00 GMT

Horse No.	Horse	Weight	Weight above Minimum (desired output)	Race No.	Course	Date
1	HE RUNS AWAY	59	5	1	Doomben	23-Oct-21
2	HONORABLE SPIRIT	59	5	1	Doomben	23-Oct-21
3	IRISH SEQUEL	58	4	1	Doomben	23-Oct-21
4	ACHIEVER	57.5	3.5	1	Doomben	23-Oct-21
5	CHOCOLATIER	54	0	1	Doomben	23-Oct-21
6	NEMINGAH	54	0	1	Doomben	23-Oct-21
7	MONEY SHOT	54	0	1	Doomben	23-Oct-21
1	MISHANI MENTAL	57	2	2	Doomben	23-Oct-21
2	PRIMED FOR VICTORY	57	2	2	Doomben	23-Oct-21
3	EXO LADY	55	0	2	Doomben	23-Oct-21
4	HONKYTONK DIVA	55	0	2	Doomben	23-Oct-21
5	SPANISH ANGELS	55	0	2	Doomben	23-Oct-21
6	FLORAL OASIS	55	0	2	Doomben	23-Oct-21
7	SHOTGUN DIVA	55	0	2	Doomben	23-Oct-21
8	OLYMPIC MOTTO	55	0	2	Doomben	23-Oct-21
1	ALREADY BLESSED	60	4	3	Doomben	23-Oct-21
3	ZINGALONG	60	4	3	Doomben	23-Oct-21
4	FROZEN IN TIME	59.5	3.5	3	Doomben	23-Oct-21
5	MORT DOYLE	59	3	3	Doomben	23-Oct-21
6	VALIANT	59	3	3	Doomben	23-Oct-21
7	BLUE ODYSSEY	58.5	2.5	3	Doomben	23-Oct-21
11	SHAMATON	57.5	1.5	3	Doomben	23-Oct-21
12	WINSIDE	57	1	3	Doomben	23-Oct-21
13	ASHZAYA	56	0	3	Doomben	23-Oct-21

Re: Dax code to calculate a value above the minimum value

Jos_Woolley — Sun, 24 Oct 2021 06:20:32 GMT

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 )

Re: Dax code to calculate a value above the minimum value

donnynz — Sun, 24 Oct 2021 07:22:07 GMT

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.]

)

Re: Dax code to calculate a value above the minimum value

Jos_Woolley — Sun, 24 Oct 2021 07:41:10 GMT

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

Re: Dax code to calculate a value above the minimum value

donnynz — Sun, 24 Oct 2021 07:48:43 GMT

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.

Re: Dax code to calculate a value above the minimum value

Jos_Woolley — Sun, 24 Oct 2021 08:06:31 GMT

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.

Re: Dax code to calculate a value above the minimum value

donnynz — Sun, 24 Oct 2021 08:32:05 GMT

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.	Horse	Race No.	Course	Date
1	HE RUNS AWAY	1	Doomben	23-Oct-21
3	IRISH SEQUEL	1	Doomben	23-Oct-21
4	ACHIEVER	1	Doomben	23-Oct-21
5	CHOCOLATIER	1	Doomben	23-Oct-21
6	NEMINGAH	1	Doomben	23-Oct-21
7	MONEY SHOT	1	Doomben	23-Oct-21
1	MISHANI MENTAL	2	Doomben	23-Oct-21
2	PRIMED FOR VICTORY	2	Doomben	23-Oct-21
3	EXO LADY	2	Doomben	23-Oct-21
4	HONKYTONK DIVA	2	Doomben	23-Oct-21
5	SPANISH ANGELS	2	Doomben	23-Oct-21
6	FLORAL OASIS	2	Doomben	23-Oct-21
7	SHOTGUN DIVA	2	Doomben	23-Oct-21
8	OLYMPIC MOTTO	2	Doomben	23-Oct-21
1	ALREADY BLESSED	3	Doomben	23-Oct-21
4	FROZEN IN TIME	3	Doomben	23-Oct-21
5	MORT DOYLE	3	Doomben	23-Oct-21
6	VALIANT	3	Doomben	23-Oct-21
7	BLUE ODYSSEY	3	Doomben	23-Oct-21
8	LYRICAL GIRL	3	Doomben	23-Oct-21
9	PLATINUM EUROS	3	Doomben	23-Oct-21
10	SOUBRETTE	3	Doomben	23-Oct-21
11	SHAMATON	3	Doomben	23-Oct-21
12	WINSIDE	3	Doomben	23-Oct-21
13	ASHZAYA	3	Doomben	23-Oct-21

Table named 'Race Data#1'

Horse No.	Horse	Carry Weight	Weight above Minimum (desired output)
1	HE RUNS AWAY	59	5
3	IRISH SEQUEL	58	4
4	ACHIEVER	57.5	3.5
5	CHOCOLATIER	54	0
6	NEMINGAH	54	0
7	MONEY SHOT	54	0
1	MISHANI MENTAL	57	2
2	PRIMED FOR VICTORY	57	2
3	EXO LADY	55	0
4	HONKYTONK DIVA	55	0
5	SPANISH ANGELS	55	0
6	FLORAL OASIS	55	0
7	SHOTGUN DIVA	55	0
8	OLYMPIC MOTTO	55	0
1	ALREADY BLESSED	60	4
4	FROZEN IN TIME	59.5	3.5
5	MORT DOYLE	59	3
6	VALIANT	59	3
7	BLUE ODYSSEY	58.5	2.5
8	LYRICAL GIRL	58.5	2.5
9	PLATINUM EUROS	58.5	2.5
10	SOUBRETTE	58	2
11	SHAMATON	57.5	1.5
12	WINSIDE	57	1
13	ASHZAYA	56	0

Hope that is more useful?

Thanks

Re: Dax code to calculate a value above the minimum value

Jos_Woolley — Sun, 24 Oct 2021 08:44:48 GMT

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

Re: Dax code to calculate a value above the minimum value

donnynz — Sun, 24 Oct 2021 08:52:57 GMT

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

Re: Dax code to calculate a value above the minimum value

Jos_Woolley — Sun, 24 Oct 2021 08:59:06 GMT

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.

Re: Dax code to calculate a value above the minimum value

donnynz — Sun, 24 Oct 2021 09:46:41 GMT

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.

Re: Dax code to calculate a value above the minimum value

Jos_Woolley — Sun, 24 Oct 2021 10:05:59 GMT

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?

Re: Dax code to calculate a value above the minimum value

donnynz — Sun, 24 Oct 2021 20:04:11 GMT

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

Re: Dax code to calculate a value above the minimum value

Jos_Woolley — Mon, 25 Oct 2021 05:22:08 GMT

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

Re: Dax code to calculate a value above the minimum value

donnynz — Mon, 25 Oct 2021 05:47:16 GMT

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.

Re: Dax code to calculate a value above the minimum value

Jos_Woolley — Mon, 25 Oct 2021 06:09:08 GMT

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

Cheers

Re: Dax code to calculate a value above the minimum value

donnynz — Mon, 25 Oct 2021 06:29:18 GMT

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