topic Re: Counting the number of non-blank values in a column in DAX Commands and Tips

Counting the number of non-blank values in a column

Anonymous — Fri, 31 Jul 2020 12:51:56 GMT

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.

Re: Counting the number of non-blank values in a column

amitchandak — Fri, 31 Jul 2020 13:12:25 GMT

Anonymous , did not get it completely , try

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

Re: Counting the number of non-blank values in a column

nandukrishnavs — Fri, 31 Jul 2020 13:13:42 GMT

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 🙂

Re: Counting the number of non-blank values in a column

Anonymous — Fri, 31 Jul 2020 13:45:08 GMT

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

Re: Counting the number of non-blank values in a column

Anonymous — Fri, 31 Jul 2020 13:51:18 GMT

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.

Re: Counting the number of non-blank values in a column

nandukrishnavs — Fri, 31 Jul 2020 14:20:38 GMT

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 🙂

Re: Counting the number of non-blank values in a column

Anonymous — Fri, 31 Jul 2020 14:22:26 GMT

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

Re: Counting the number of non-blank values in a column

Anonymous — Fri, 31 Jul 2020 14:24:15 GMT

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?

Re: Counting the number of non-blank values in a column

nandukrishnavs — Fri, 31 Jul 2020 14:47:53 GMT

Anonymous

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

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

Re: Counting the number of non-blank values in a column

Anonymous — Sat, 01 Aug 2020 08:03:32 GMT

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