Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 |
Solved! Go to Solution.
This is what I'm getting:
Proud to be a Super User!
Paul on Linkedin.
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)
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
You are welcome. If my reply helped, please mark it as Answer.
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.
I am very confused about what you want. Share your tables and clearly show the expected result.
How is your model set up and which fields are you using in the visual?
Proud to be a Super User!
Paul on Linkedin.
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.
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])
Proud to be a Super User!
Paul on Linkedin.
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"
This is what I'm getting:
Proud to be a Super User!
Paul on Linkedin.
Please provide a depiction of the expected outcome. It will help to understand exactly what is needed. Thanks!
Proud to be a Super User!
Paul on Linkedin.
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.
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))
Proud to be a Super User!
Paul on Linkedin.
Thank you for your time Paul. This time it shows a blank always.
Try:
More than 2 =
VAR _ CR = COUNTROWS (ALLEXCEPT (Table, Table[Customer]))
RETURN
COUNTROWS(FILTER(VALUES(Table[Customer]), _CR > 2))
Proud to be a Super User!
Paul on Linkedin.
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)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
211 | |
86 | |
64 | |
59 | |
56 |