Count Rows When a Value occurs more than X times

[AndresOH]

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

[hellybobs82]

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.

[hellybobs82]

Thank you so much for taking the time to help me, can't say how greatful I am! It worked wonderfully!

Helen

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[Ashish_Mathur]

Hi,

This measure works

Measure = countax(FILTER(VALUES(Data[Country]),COUNTROWS(Data)>2),Data[Country])

Hope this helps.

[AndresOH]

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.

[AndresOH]

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.

[Ashish_Mathur]

I am very confused about what you want.  Share your tables and clearly show the expected result.

[PaulDBrown]

How is your model set up and  which fields are you using in the visual?

[AndresOH]

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])

[AndresOH]

I'm not really sure why it's nor working, the first measure returns blank and about the card's one I get this message: "... the function SUMX cannot work with values of type boolean"

[PaulDBrown · Accepted solution]

This is what I'm getting:

[PaulDBrown]

Please provide a depiction of the expected outcome. It will help to understand exactly what is needed. Thanks!

[AndresOH]

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

[AndresOH]

Thank you for your time Paul. This time it shows a blank always.

[PaulDBrown]

Try:

More than 2 =
VAR _ CR = COUNTROWS (ALLEXCEPT (Table, Table[Customer]))

RETURN

COUNTROWS(FILTER(VALUES(Table[Customer]), _CR > 2))

[AndresOH]

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)