Counting the number of non-blank values in a column

[Anonymous]

Hi Guys,

I am working on a cricket dataset and need to calculate the number of times a player got dismissed in a particular year. This is required for building the player stats where I calculate the player wise number of runs, average and strike-rate.  However, the data is in the below form:

The above shows only filtered rows for balls faced by Rayudu. He is the player dismissed 13 out of 14 times and there is another batsman who got dismissed (Watson) on one of the occassion.

If i show the filtered view on the occasions when Rayudu got dismissed, we can see that on one of the occasions, the batsman facing is MS Dhoni. I am trying to understand how to create a measure which can calculate that Rayudu was dismissed 14 times in the current year. 

While making the visualisation i would be making a table where i will select a player, his total runs, strike rate and average. This number of time a player got dismissed will be used for calculation of the average i.e. total runs/ no of time dismissed. 

I tried the below expression:  

no_dismissed = CALCULATE(COUNT(deliveries[player_dismissed]),FILTER(ALL(deliveries[player_dismissed]),deliveries[player_dismissed]<> ""))

 But it returns the count of all the occassions when there was a dismissal when that player was batting i.e. the count of the rows in the first image. However, i would like to calculate the no of rows in the second image.

Thanks in advance. 

[nandukrishnavs]

@Anonymous 

Try this 

no_dismissed =
VAR __player =
    SELECTEDVALUE ( deliveries[batsman] )
VAR __result =
    CALCULATE (
        COUNT ( deliveries[player_dismissed] ),
        FILTER (
            ALL ( deliveries[player_dismissed] ),
            deliveries[player_dismissed] = __player
        )
    )
RETURN
    __result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

@nandukrishnavs

This gives me a value of 13 for Rayadu.

I think it is not taking into account the dismissal where the batsman was Dhoni and Rayudu got dismissed. 

Can you help incorporate that in the count somehow.

[harshnathani]

Hi @Anonymous ,

You need to have a seperate table for Players which is the dimension table.

Create the slicer from the Player's table and then follow the formula above. It should give you the value for AT Rayadu.

Incase you need some inspirations on the design.

https://community.powerbi.com/t5/Data-Stories-Gallery/IPL-Analysis-The-Criclytics/td-p/156504

https://www.youtube.com/watch?v=ujPuXZT7F6o&feature=youtu.be

Regards,

Harsh Nathani

[nandukrishnavs]

@Anonymous 

no_dismissed =
VAR __player =
    SELECTEDVALUE ( deliveries[batsman] )
VAR __result =
    CALCULATE (
        COUNT ( deliveries[player_dismissed] ),
        FILTER ( ALL ( 'deliveries' ), deliveries[player_dismissed] = __player )
    )
RETURN
    __result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

This takes away my year filter and gives me the count of how many times Rayadu got dismissed over the entire period of 12 years I am analysing (IPL seasons).

Can you also tell me how to avoid that?

[harshnathani]

Hi @Anonymous ,

Assume you have a Date Table too.

Incase not, you should try adding a Date Table.

Create a Date Table
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://www.sqlbi.com/articles/mark-as-date-table/

Regards,

Harsh Nathani

[nandukrishnavs]

@Anonymous 

Instead of ALL() function, use ALLEXCEPT(). Then specify the Year column.

https://docs.microsoft.com/en-us/dax/allexcept-function-dax

[amitchandak]

@Anonymous , did not get it completely , try

no_dismissed = CALCULATE(COUNT(deliveries[player_dismissed]),FILTER(ALL(deliveries[player_dismissed]),not(isblank(deliveries[player_dismissed]))))

[Anonymous]

This returns the number of bowls faced by that batsman somehow.