Count rows with condition

[Natalie123455]

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

[HotChilli · Accepted solution]

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 

[HotChilli · Accepted solution]

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 

[Natalie123455]

Thanks @HotChilli and @v-tangjie-msft 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!

[v-tangjie-msft · Accepted solution]

Hi @Natalie123455 ,

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. 

[Natalie123455]

Hi @v-tangjie-msft,  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?

[v-tangjie-msft]

Hi   @Natalie123455 ,

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.

[HotChilli]

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