Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Count the number of times the key and value has been repeated (recurrence)

Hello everyone. I need help with the following question.

 

In the dataset, I evaluate things like "good" or "bad". We need to count the times that "bad" responses repeats itself more than once for the place and the item being evaluated.


This is an example: 
* Bad Responses are items evaluated as bad: Bad Responses

Bad Responses = CALCULATE( COUNTROWS (Table[Response]), Response == 'Bad')

 

* Wrong Measure is the number of times "Bad Response" appeared more than once. I thought it would work as (Bad Response - 1) as it is right now, but the grand total is wrong - I suspect that it is calculating "[Bad Responses] -1",  subtracting = the totals, and not the sum of the row values as I need it to.

Captura de tela 2022-11-21 194157.png


Sample data below:

IDPlaceItem NameResponse
1CopacabanaItem 1Bad
2CopacabanaItem 1Bad
3CopacabanaItem 1Bad
4CopacabanaItem 2Bad
5CopacabanaItem 2Bad
6Corporate ParkItem 1Bad
7Corporate ParkItem 2Bad
8Corporate ParkItem 2Bad

 

The expected output is:

PlaceBad RatingsRecurrence 
Copacabana53
Corporate Park31
TOTAL84

 

The 4 in Recurrence is the 2 times "Item 1" in Copacabana was rated "bad" beyond the first bad rating, plus one bad rating to "Item 2" beyond the first, and plus one bad rating from "Item 2" on Corporate Park beyond the first bad rating on Item 2. 

 

I tried constructing a dimension table and doing a sum, but the date property of the data would not function correctly when using slicers.

 

 

Thanks in advance!

 

1 ACCEPTED SOLUTION
v-yadongf-msft
Community Support
Community Support

Hi @Anonymous ,

 

Please create following measures:

 

Count_bad = CALCULATE(COUNT('Table'[Response]),ALL('Table'[Item Name]))

Count_bad-1 = IF(COUNT('Table'[Response]) >1,COUNT('Table'[Response])-1)

Count_all_bad-1 = SUMX(ALL('Table'[Item Name]),[Count_bad-1])

Count_sum_bad-1 = SUMX(ALL('Table'[Place]),[Count_all_bad-1])

Bad_response = IF(ISINSCOPE('Table'[Place]), [Count_all_bad-1],[Count_sum_bad-1])

 

 

You will get the result you want:

vyadongfmsft_0-1669102147045.png

 

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yadongf-msft
Community Support
Community Support

Hi @Anonymous ,

 

Please create following measures:

 

Count_bad = CALCULATE(COUNT('Table'[Response]),ALL('Table'[Item Name]))

Count_bad-1 = IF(COUNT('Table'[Response]) >1,COUNT('Table'[Response])-1)

Count_all_bad-1 = SUMX(ALL('Table'[Item Name]),[Count_bad-1])

Count_sum_bad-1 = SUMX(ALL('Table'[Place]),[Count_all_bad-1])

Bad_response = IF(ISINSCOPE('Table'[Place]), [Count_all_bad-1],[Count_sum_bad-1])

 

 

You will get the result you want:

vyadongfmsft_0-1669102147045.png

 

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Greg_Deckler
Super User
Super User

@Anonymous Try:

Measure = 
  VAR __Table = 'Table'
  VAR __Table1 = GROUPBY(FILTER(__Table,[Item],[Response] = "Bad"),"__Count",COUNTX(CURRENTGROUP(),[ID]))
  VAR __Rows = COUNTROWS(__Table1)
  VAR __Result = SUMX(__Table1,[__Count]) - __Rows
RETURN
  __Result

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors