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 100 messages should be considered.

 

 
 

 

This works well!

 

Now, I would like to create a card that shows the total distinct count of OutletIDs that have sent more than 100 messages. For doing so, I created three measure

 

# Outlets = DISTINCTCOUNT(tablename1[OutletId])

# Messages = DISTINCTCOUNT(tablename2[MessageId])

# Outlets over 100 Messages = CALCULATE([# Outlets], FILTER(tablename2,[# Messages] > 100 ))

 

It unfortunately does not work. Any idea of what I could do? Many thanks for any help in advance!

 

Best,

Natalie

 

 

 

 

 

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. 

6 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    3 years ago

    What is the granularity of the base table?  What does it look like? 

    If you provide some sample data (just make some dummy data, not too complicated), we can help.  Please show what the desired result is (because I'm reasonably sure we don't want all the DISTINCTCOUNTs)

    Let's change the criteria to be >2 messages per outlet instead of 100

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks HotChilli and Anonymous for your responses!

    Please find a sample of the data below: It shows OutletIDs and the MessageIDs (called MessageOid in the table) of messages that were sent per outlet.

    As you can see in the table above, Outlet 008 sent 17 messages. The table below again reflects this: It counts the disctintive message IDs per outlet to find out how many messages where sent per outlet.

     

    Now, I struggle to display in a card the sum of outlets which sent over 100 distinctive messages. For example, Outlet 02 is one such outlet (it sent 1440 messages) - Outlet 08 is NOT such outlet (it sent only 17 messages).

     

    Please let me know if there is anything unclear!

    Thanks a lot for your help!

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      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,  sorry for responding so late!! I just stumbled across this case again and played a bit around with it. Your measure works great - thanks a lot!! Unfortunately, having a large dataset, the measure takes a pretty long time to load - is there any way to make the measure more efficient?

  • HotChilli's avatar
    HotChilli
    Community Champion
    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