Forum Discussion

AndresOH's avatar
AndresOH
Frequent Visitor
4 years ago
Solved

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, th...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    This is what I'm getting:

     

AndresOH's avatar
AndresOH
Frequent Visitor
4 years ago

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's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
4 years ago

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