Calculate count of rows

[ajinkya1907]

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 of times winner appears in the team1 or team2, I should get the answer.

I am looking for Match_count field of below dashboard

[ajinkya1907 · Accepted solution]

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]

@Zubair_Muhammad, Could you please help with your expertise?

[Zubair_Muhammad]

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]

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 · Accepted solution]

HI @ajinkya1907

Try this MEASURE

Median = MEDIANX(ALLSELECTED(matches[winner]),[Matches Played])

[ajinkya1907]

Dear @Zubair_Muhammad, Thanks Again.  I missed this view 🙂

[Zubair_Muhammad]

Hi @ajinkya1907

Following post contains some good resources for Power BI Learning

http://community.powerbi.com/t5/Desktop/Power-BI-Learning/m-p/336089/highlight/true#M150393

[ajinkya1907 · Accepted solution]

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]

Yes It make sense. I tried using your suggestion 

MatchCnt1 = CALCULATE(COUNTROWS(matches),USERELATIONSHIP(DistinctTeams1[team1],matches[team1]))

It gives me count of winners not count of times team appears in Team1. So problem still exists.

[Michiel]

The thing is, the row labels in a table, like your preview, have to come from a column in the model and act as filter to the calculations. In your example, when you use the 'winner' column, you will not have matches in the context where the team was the losing team. So you will not be able to calculate the total number of matches.

This only changes when you take the team out of the data table in a separate table. This is not a work around, but a matter of sound model design. Doing this, you can use calculated measures like the ones I wrote in my earlier comment.

[ajinkya1907]

Hello Micheal,

Thanks for your suggestion. Though It doesn't solve my purpose. 

This is normalised table & doesn't make any sense to create another table just to find work around. Good solution will be if we can find the answer by using calculated measures or some other turn around.

[Michiel]

I don't think this is an issue really, you'd better post these questions in the community forum.

But anyway, for this to work you should have a separate table in your model containing the teams, and have relationships both from team1 to the team table and from team2 to the team table. Now,

Match_Count = [Count1] + [Count2]

where

Count1 = COUNTROWS(Data)

and

Count2 = CALCULATE(COUNTROWS(Data), USERELATIONSHIP(Data[Team2], Team[Team]))

assuming your main data table is called Data, and your team table is called Team with column Team.

This also assumes that no team plays against itself, which seems reasonable.