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] ) ) )
)
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    ajinkya1907

     

    So if you have three Columns already for Teams 1 and 2 and winner. This will give you the Runner Up Name

     

  • ajinkya1907's avatar
    ajinkya1907
    Helper II
    8 years ago

    Thanks for your quick response. Appreciate it !!

     

    The solution which you provided gives attached error. Also we need to find out runner up as per last match in every year :-)

     

    Data set is available at - https://www.kaggle.com/manasgarg/ipl/data

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago
      Add it as a calculated column.
      Not as a measure
      • ajinkya1907's avatar
        ajinkya1907
        Helper II
        8 years ago

        Thanks Zubair for your quick response.

         

        I was looking for to achieve it with calculated measure. As no solution I can think about, I created new column as below

        IPL_RunnerUp2 = IF(matches[team2]<>matches[winner],matches[team2],matches[team1])

         

        and on that I applied max date logic as per your earlier suggestion for winner team. It worked fine for Runner up team. Thanks for your help.