Forum Discussion

ajinkya1907's avatar
ajinkya1907
Icon for Helper II rankHelper II
8 years ago
Solved

Calculate count of rows

Hi,   I am having below dataset   I would like to identify number of matches played by every winning team. Please note winning team can be part of Team 1 OR team 2. So if I calculate number...
Dashboards
  • ajinkya1907's avatar
    ajinkya1907
    8 years ago

    Dear Zubair_Muhammad, Thanks again for your help. It's indeed almost perfect solution :-)

     

    I made minor changes as I could not get SELECTEDVALUE function. Rest is all good.

     

    Match Count of Winner Team =
    VAR winnerteam =
        SELECTCOLUMNS( matches,"winner2",matches[winner])
    RETURN
        CALCULATE ( COUNTROWS ( ALLSELECTED ( Matches ) ), Matches[Team1] = winnerteam )
            + CALCULATE ( COUNTROWS ( ALLSELECTED ( Matches ) ), Matches[Team2] = winnerteam )

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    HI ajinkya1907

     

    Try this MEASURE

     

    Median = MEDIANX(ALLSELECTED(matches[winner]),[Matches Played])
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi ajinkya1907

 

Try this MEASURE. Assuming TableName is Matches

 

Match Count of Winner Team =
VAR winnerteam =
    SELECTEDVALUE ( Matches[Winner] )
RETURN
    CALCULATE ( COUNTROWS ( ALLSELECTED ( Matches ) ), Matches[Team1] = winnerteam )
        + CALCULATE ( COUNTROWS ( ALLSELECTED ( Matches ) ), Matches[Team2] = winnerteam )
  • ajinkya1907's avatar
    ajinkya1907
    Icon for Helper II rankHelper II
    8 years ago

    Dear Zubair_Muhammad, Thanks again for your help. It's indeed almost perfect solution :-)

     

    I made minor changes as I could not get SELECTEDVALUE function. Rest is all good.

     

    Match Count of Winner Team =
    VAR winnerteam =
        SELECTCOLUMNS( matches,"winner2",matches[winner])
    RETURN
        CALCULATE ( COUNTROWS ( ALLSELECTED ( Matches ) ), Matches[Team1] = winnerteam )
            + CALCULATE ( COUNTROWS ( ALLSELECTED ( Matches ) ), Matches[Team2] = winnerteam )

  • ajinkya1907's avatar
    ajinkya1907
    Icon for Helper II rankHelper II
    8 years ago

    Dear Zubair_Muhammad,

     

    I wanted to calculate median or max of matches played. I will use that to complete point table. I used your earlier solution to calculate mathes played. Let us say for below screenshot I was looking for a measure which will give me value 14

     

    I tried using below function I am getting an error 

    MaxVal=
    VAR winnerteam = 
    SELECTCOLUMNS( matches,"winner2",matches[winner])
    var temp =
    FILTER(SUMMARIZE(matches,
    matches[winner],
    "cals", CALCULATE ( COUNTROWS ( ALLSELECTED ( Matches ) ), FILTER(ALLSELECTED(matches),Matches[Team1] = winnerteam && matches[Match_Type]="Normal"))
    + CALCULATE ( COUNTROWS ( ALLSELECTED ( Matches ) ), FILTER(ALLSELECTED(matches),Matches[Team2] = winnerteam && matches[Match_Type]="Normal"))),1)
    RETURN
    MAXX(TOPN(1,temp,[cals],desc),[cals])

     

     

    Also I am getting one additional blank row which I don't want :- (

     

    Zubair_Muhammad, Could you please recommed some good tutorial or article where I can learn DAX. If you take lectures, count me in as your student :-)

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      HI ajinkya1907

       

      Try this MEASURE

       

      Median = MEDIANX(ALLSELECTED(matches[winner]),[Matches Played])
      • ajinkya1907's avatar
        ajinkya1907
        Icon for Helper II rankHelper II
        8 years ago

        Dear Zubair_Muhammad, Thanks Again.  I missed this view :-)