Forum Discussion
Count Rows When a Value occurs more than X times
Hi everyone!
I'am trying to count the rows in a table only when the code of a customer appears more than "x" times. In this particular case the number is 2.
Taking as an example the table below, the measure should show the value = 3, because is the number of rows in the table that contains customer that appears more than 2 times.
Could anyone help me with this?
| Customer | Country |
| AAA | USA |
| AAA | USA |
| BBB | Brazil |
| DDD | Germany |
| AAA | USA |
This is what I'm getting:
19 Replies
- Ashish_Mathur
Super User
Hi,
This measure works
Measure = countax(FILTER(VALUES(Data[Country]),COUNTROWS(Data)>2),Data[Country])Hope this helps.
Hello Ashish!
I have the problem that this measure don't let me filter another another date. I need to make the calculation for each month selected in a slicer, this way it's not working, it also doesn't allow me to filter for customer type.
Hi Ashish!
Sorry I said something wrong in the last post, the measure allows me to filter by customer type, the problem is that is not filteren by date.
PaulDBrownCommunity Champion
Try:
More than 2 =
VAR _ CR = COUNTROWS (ALLEXCEPT (Table, Table[Customer]))RETURN
COUNTROWS(FILTER(VALUES(Table[Customer]), _CR > 2))
Hi Paul! Thank you for your answer.
It didn't work, maybe it's my fault because I didn't put the whole information. Could you please see the table below?
Customer Country Type Month AAA USA 1 April AAA USA 1 April BBB Brazil 2 April DDD Germany 2 April AAA USA 1 June I need to make this calculation and at the same time filter the type of customer. If I choose type 1 it should count the rows of customer type 1 when it occurs more than 2 time. And also I need to be able to filter for an specific month (that will be select with a slicer)
- PaulDBrown
Please provide a depiction of the expected outcome. It will help to understand exactly what is needed. Thanks!
In this case, for example if I choose "April" and customer type = 1, the measure should show 2, because there are only 2 rows when an specific customer is shown 2 times or more in that month. If I choose june or customer type = 2 the measure should show 0 because there are no customer that appers 2 times in that period.
- PaulDBrown
Ok. Try:
more than or equal 2 =
VAR _SelCust = MAX(Table[Customer])
VAR _CR = COUNTROWS(FILTER(ALLSELECTED(Table), Table[Customer] = _SelCust)
RETURN
COUNTROWS(FILTER(VALUES(Table[Customer]), _CR >= 2))
- PaulDBrown
How is your model set up and which fields are you using in the visual?
I have two tables, one is customers and the other is calendar, this second from which I filter the dates.
If I use a table as a visual I can just count the rows and with the filters pane I can select a simple measure of count rows of the customer table and select the ones that are greater than 2, this works in a table.
The problem is that I also need to use a card in which the filter pane doesn't work, what I'm trying to do is create a measure for the card visual.Please let me know if it's clear.
- PaulDBrown
Ok, this seems to work:
First an intermediate measure to count rows:
Customer rows >1 = VAR _SelCust = MAX ( FTable[Customer] ) VAR _CR = COUNTROWS ( FILTER ( ALLSELECTED ( FTable ), FTable[Customer] = _SelCust ) ) RETURN COUNTROWS ( FILTER ( FTable, _CR >= 2 ) )And the final measure for the card:
Card Measure = SUMX(FTable, [Customer rows >1])
Hi
Sorry, I wasnt sure if should start a new thread or not, new to alot of this. I have been trying to use the dax provided above but it isn't quite working for me (maybe i misunderstood the dax)
+3 = countax(FILTER(VALUES('Attendance Marks'[External Id]),COUNTROWS('Attendance Marks')>=3),'Attendance Marks'[External Id])I want to try to return a number for how many times an External ID appears more than 3 times in a table. At the moment it returns how many unique ID's I have which is 39, but i want to know how many ID appear more than 3 times the number will be around 5.I then need one that will return:How many ID's occur more than 3 times when the subject is filtered to Registration.Any help is greatly appreciated!- Ashish_Mathur
Hi,
Try these measures
Total = countrows('Attendance Marks')
More than 3 = COUNTROWS(FILTER(VALUES('Attendance Marks'[External Id]),[Total]>=3))
Hope this helps.
Thank you so much for taking the time to help me, can't say how greatful I am! It worked wonderfully!
Helen
