Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count Rows + greater than

Hi!
Looking for some guidance with simple DAX Calculation.I created one Measure to count rows:

 

Measure = COUNTROWS(FullTable)

 

Then on a matrix table I add the following:

 

Lines:
Account Number
Transaction Number
Date of Transaction

 

Values:
Current Value
Measure


I want this table only to show "Account Numbers" that had more than 30 rows count. I just added a filter in the visual for measure field to show me "is greater than 30" but the table doesn't apply this filter.

 

Feel kind stupid since this sounds basic. But I fell this has something to do with those 2 extra lines I added (Transaction Number / Date of Transaction).

 

Can you guys help me?

Thanks,
Barbara

  • VasTg's avatar
    VasTg
    6 years ago

    Anonymous 

     

    There must be a better way to achieve this, but here you go.

     

    a. Create a new column in the table with value 1

                New_Column = 1

    b. Use the below measure to count the number of Accounts.

     

    Accts Cnt = SUMX(SUMMARIZE('Blank Report','Blank Report'[Country],"ABC",CALCULATE(SUM('Blank Report'[Column]),ALLEXCEPT('Blank Report','Blank Report'[Country]))),[ABC])
     
    Replace 'Blank Report' with you table name and Country with Accounts column.
     
    If it helps, mark it as a solution.
    Kudos are good too.

     

3 Replies

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    Anonymous 

     

    There must be a better way to achieve this, but here you go.

     

    a. Create a new column in the table with value 1

                New_Column = 1

    b. Use the below measure to count the number of Accounts.

     

    Accts Cnt = SUMX(SUMMARIZE('Blank Report','Blank Report'[Country],"ABC",CALCULATE(SUM('Blank Report'[Column]),ALLEXCEPT('Blank Report','Blank Report'[Country]))),[ABC])
     
    Replace 'Blank Report' with you table name and Country with Accounts column.
     
    If it helps, mark it as a solution.
    Kudos are good too.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi VasTg, How are you? Me again. I had worked perfected before but now I only need to show the distint count for "Transaction Numbers" and then filter it. In this formula you gave me, how do I changed it? Thanks, Barbara

      • VasTg's avatar
        VasTg
        Memorable Member
        6 years ago

        Anonymous 

         

        I am doing good. Thanks for asking.

         

        Do you mind share a sample input and output data?

         

        Thanks,

        Vas