Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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:
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:
I'm getting the red squiggle at the start of the code line.
Again thanks for any help.
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
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.
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?
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. | 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
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:
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?
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 |
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
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |