Forum Discussion

ajinkya1907's avatar
ajinkya1907
Helper II
8 years ago
Solved

Problem in selecting Max date

  I have a table "matches" with below shown columns     I have created calculated field Max_date = MAX(matches[date])   I have created a filter based on year. I want team1 value to be di...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    ajinkya1907

     

    Try this MEASURE to get the TEAM1 on maximum date

     

    Team1 On MaxDate =
CALCULATE (
    FIRSTNONBLANK ( Matches[Team1], 1 ),
    FILTER ( ALL ( Matches[Date] ), Matches[Date] = MAX ( Matches[Date] ) )
)
ajinkya1907's avatar
ajinkya1907
Helper II
8 years ago

It's RCB :-)

 

I have another query for you.. I have 3 columns name team1 & team 2 playing against each other. Finally winner column to indicate which team won the match. Using max date we identified the winning team. Any suggestions on how to calculate the runner up?

 

One solution which I can think is to add column in the data model where team <> winner team. But If I do not prefer to make any data model changes then what could be the solution?

 

I am new to power BI. whenever I try to use IF statement, It doesn't show the columns like team 1 or team 2, do you know how to make those columns available in if statement? e.g. I was thinking to concatenate both the columns like if (winner team = team1, team2, team 1)  for max date by the formula you gave.

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

ajinkya1907

 

Try this calculated column to get the Runner Up Name

 

RunnerUp =
EXCEPT (
    UNION (
        ROW ( "Myteams", CALCULATE ( VALUES ( Matches[Team1] ) ) ),
        ROW ( "Myteams", CALCULATE ( VALUES ( Matches[Team2] ) ) )
    ),
    ROW ( "Myteams", CALCULATE ( VALUES ( Matches[Winner] ) ) )
)