Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good morning,
I am very new to Power BI and I am building an App to analyze the results of my wife and my Board Game matches as a mean of learning and exposure to the Application.
I have the following game data:
I would like to build calculated columns that identify, in each game, which player won Science, Military, Wonders etc.
I used VBA in Excel to calculate the Results column (that identifies the higher total in each Game ID). But, I would much prefer to create these columns in Power BI as I feel it offers a great opportunity for learning.
Also, please be aware that in certain games, there are more than just two players (in case that affects the solution)
Please, could someone offer a suggestion as to the best method to accomplish this?
Kind regards,
Paul
Solved! Go to Solution.
Hi @paulvans182 ,
You can try to create a calculated column like the one below:
Result =
VAR _max =
CALCULATE ( MAX ( 'Table'[Total] ), ALLEXCEPT ( 'Table', 'Table'[Gameid] ) )
RETURN
IF ( 'Table'[Total] = _max, "W", BLANK () )
Results are as follows:
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @paulvans182 ,
You can try to create a calculated column like the one below:
Result =
VAR _max =
CALCULATE ( MAX ( 'Table'[Total] ), ALLEXCEPT ( 'Table', 'Table'[Gameid] ) )
RETURN
IF ( 'Table'[Total] = _max, "W", BLANK () )
Results are as follows:
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Good morning Joey,
Thanks for your response. I apologize for my delay in getting back to you. Your solution works perfectly.
Out of interest, I used a different method and I was wondering whether you could tell me whether it is an appropriate choice idea (from a modelling perspective).
I decided that I should try and only have one Value column - ie: Points. And each different category should be housed in a category column.
I, therefore, did the following:
1. I unpivoted the columns of the table 'Game Data'
2. Duplicated my 'Game Data' query and grouped by Game ID and Category keeping the MAX points from each - renaming this query as 'Category Data'
3. I added a custom column identifying these MAX values as "W" - named Category Result
4. I merged 'Game Data' and 'Category Data' joining on Game ID, Points, Category, bringing in the Category Result column and filling all null values as "L"
This solution appears to have worked perfectly. My only concern is that I am unsure of the efficiency of this solution because my data table is relatively small.
Once again, thank you for your solution - and for any feedback you have related to mine.
Kind regards,
Paul
Hi @paulvans182 ,
Your solution is really feasible and effective. From a model perspective, using M code in power query is better than using DAX code.
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |