How to count number of occurrence of a value in a column

[pzcannon]

Hi,

I'm pretty new to powerBI and I've been trying out DAX to count the number of times a Date (DD/MM/YYYY) appears in a column.

Basically, I have a table with the following columns: Issue, Date Reported, Date Solved. I'm trying to count the number of items reported and addressed on a certain date.

Date	#Reported	#Solved
1/1/2021
2/1/2021
3/1/2021
4/1/2021

Can someone help me out. Thanks!

Sincerely,

Patrick

[Jihwan_Kim]

Hi, @pzcannon 

Try something like below.

Reported count measure =
CALCULATE (
COUNTROWS ( 'your table name' ),
'your table name'[yourcolumnname] = "Reported"
)

Solved count measure =
CALCULATE (
COUNTROWS ( 'your table name' ),
'your table name'[yourcolumnname] = "Solved"
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[pzcannon]

Hi, @Jihwan_Kim.

Thanks for your response. I think I didn't word it properly.

What I'm trying to do is to generate Table 2 from Table 1:

Table 1:

# Description Date Reported Date Solved
1 Issue 1 1-Jan 1-Jan
2 Issue 2 1-Jan 2-Jan
3 Issue 3 2-Jan 2-Jan
4 Issue 4 2-Jan 3-Jan
5 Issue 5 4-Jan 4-Jan

Table 2:

Date Items Reported Items Solved
1-Jan 2 1
2-Jan 2 2
3-Jan 0 1
4-Jan 1 0
5-Jan 0 1

Really appreciate your help. Thanks!

Sincerely,

@pzcannon

[v-yiruan-msft · Accepted solution]

Hi @pzcannon ，

I created a sample pbix file for you, please check whether that is what you want.

1. Create a date dimension table

Date = CALENDAR(MIN('Table'[Date Reported]),MAX('Table'[Date Solved]))

2. Create 2 measures as below to get the counts of item reported and item resolved

Items Reported = CALCULATE(DISTINCTCOUNT('Table'[# Description]),FILTER('Table','Table'[Date Reported]=SELECTEDVALUE('Date'[Date])))+0

Items Solved = CALCULATE(DISTINCTCOUNT('Table'[# Description]),FILTER('Table','Table'[Date Solved]=SELECTEDVALUE('Date'[Date])))+0

Best Regards

[Jihwan_Kim]

Hi, @pzcannon 

Thank you for your feedback.

I am not sure if I understood your second question correctly, but please check the below.

Items Reported =

CALCULATE (

COUNTROWS ( 'Table' ),

USERELATIONSHIP ( 'Calendar'[Date], 'Table'[Date Reported] )

)

Items Solved =

CALCULATE (

COUNTROWS ( 'Table' ),

USERELATIONSHIP ( 'Calendar'[Date], 'Table'[Date Solved] )

)

https://www.dropbox.com/s/n0s3qlot6m5awo9/pzcannon.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM