Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Count rows with condition

Hi all,   to provide you an understanding of the data: I created a table to list each Outlet ID and the corresponding number of messages that were sent per outlet. Only outlets, who sent more than ...
Need Help
Tips and Tricks
  • HotChilli's avatar
    HotChilli
    3 years ago

    Right, we have each individual call in the table so, to get what you want in one measure, we need to :

    create a variable that represents a table with each outlet and the COUNT of calls  (that's what you have in the table visual in the original post).

     
    var _tb = SUMMARIZECOLUMNS(TableCalls[OutletID], "numCalls", COUNT(TableCalls[Message]))

    and then

    we need to FILTER this table to get only rows where the count is greater than 100 and we COUNT those rows.

    All this is in a measure.  I leave you to complete the RETURN statement.

    Also, change the table, column names to suit your data.

    --

    If you get stuck, I will help 

     

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    Do you mean counting the number of outlets with messages over 100?

    Please try this measure:

     

    CountMeasure = 
var _a = CALCULATE(DISTINCTCOUNT('Table'[Outletld]),FILTER('Table',CALCULATE(DISTINCTCOUNT('Table'[MessageOid]),FILTER('Table',[Outletld]=EARLIER('Table'[Outletld])))>100))
return _a

     

    Then the result is as follows.

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi   Anonymous ,

 

According to your description, you need to get the sum total with filtering on a card visual.

You can try to create a measure use this dax:

 

 

 

Measure  = 
var _t =FILTER(ADDCOLUMNS( ALLSELCTED('Table'[OutletId]) ,"Messages",[#Messages]) , [Messages]>100)
return
SUMX(_t,[Messages])

 

 

 

If this method does not meet your needs, you can provide us with your special sample data and thedesired output sample data in the form of tables, so that we can better help you solve the problem.

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.