Union Columns by Row Value

[Anonymous]

Hello everyone,

I am quite new in DAX. I had a problem to select column according to row value.

Here is an example data:

Every student has scores in 5 different sport category. I want to write a DAX formula to create "Favorite Sport Score" column. In Excel, Match&Index formulas solves that but I am stucked in Power BI

[Anonymous · Accepted solution]

@Anonymous I have an approach to solve your problem.

Measure = 
VAR _favorite = MAX('Table'[Favorite Sport])
RETURN SWITCH(TRUE()
                ,_favorite="gymnastics",SUM('Table'[gymnastics])
                ,_favorite="Cycling",SUM('Table'[Cycling])
                ,_favorite="Basketball",SUM('Table'[Basketball])
                ,_favorite="Soccer",SUM('Table'[Soccer])
                ,_favorite="Running",SUM('Table'[Running])
                ,0
)

Please let me know if you have question. 

If it solve your purpose mark it as solved.

[Anonymous]

Hello @Anonymous ,

Thank you for your answer but that code didn't work for me 🙄

"VAR _favorite = MAX('Table'[Favorite Sport])"
This line generates same value for every row.

[Anonymous]

@Anonymous did you create a measure or calculated column?

You should create a measure. Please let me know if you need calculated column

[Anonymous]

I had created calculated column. Now I tried to create maesure and it worked.

Thank you very much for the help.