cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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:

* 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.

Sample data below:

 ID Place Item Name Response 1 Copacabana Item 1 Bad 2 Copacabana Item 1 Bad 3 Copacabana Item 1 Bad 4 Copacabana Item 2 Bad 5 Copacabana Item 2 Bad 6 Corporate Park Item 1 Bad 7 Corporate Park Item 2 Bad 8 Corporate Park Item 2 Bad

The expected output is:

 Place Bad Ratings Recurrence Copacabana 5 3 Corporate Park 3 1 TOTAL 8 4

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.

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

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

You will get the result you want:

Best regards,

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

2 REPLIES 2
Community Support

Hi @Anonymous ,

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

You will get the result you want:

Best regards,

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

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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors