Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Union Columns by Row Value

Hello everyone,

 

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

 

Here is an example data:

table.jpg


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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

@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
Not applicable

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
Not applicable

@Anonymous did you create a measure or calculated column?

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

Anonymous
Not applicable

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

Thank you very much for the help.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.