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
paulvans182
Helper III
Helper III

Calculated Columns: Highest Value Flag by Row

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:

Game DataGame 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

1 ACCEPTED SOLUTION
v-joesh-msft
Solution Sage
Solution Sage

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:

1.PNG

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EUC6PL4o9ChPkWLRZ-...

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.

View solution in original post

3 REPLIES 3
v-joesh-msft
Solution Sage
Solution Sage

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:

1.PNG

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EUC6PL4o9ChPkWLRZ-...

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'

Unpivot.PNG

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

Category Result.PNG

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"

Merge Queries.PNG

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.

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.