Count based on multiple detail rows

Question

Date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; RJC# &nbsp; ACP# &nbsp; WK#1/3/2019 &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 41/3/2019 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; 41/2/2019 &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 31/2/2019 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; 31/2/2019 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; 31/1/2019 &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 21/1/2019 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; 21/1/2019 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; 21/1/2019 &nbsp; &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp;what we want to find out is the count of one RJC: for every date within last 30, 60 90 days, we need to count RJC# where Sum(RJC#) for a given date should be less than or equal to 1.&nbsp;&nbsp;for 1/1/2019, RJC# is 3 and 4th one is ACP# so it should be treated as 0. Where as for 1/3/2019, RJC# is 1 so it should be treated as 1. And sum up for the rest of the dates to one field. Like; if we have a case like 1/3/2019 and 1/04/2019, then the count should be returned as 2 else 1.&nbsp;Pls advise.

v-xuding-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
Can you please tell us what the given date mean? Do you have a slicer of date? I create a sample which calculate count of RJC without silcer. Please try and check if the result is what you want.

Creaet a calculated column.

Column = CALCULATE(SUM('Table'[RJC]),FILTER('Table','Table'[Date] = EARLIER('Table'[Date])))

&nbsp;Create a measure.

Measure = CALCULATE(DISTINCTCOUNT('Table'[Column]),'Table'[Column]&lt;=1)
&nbsp;
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ashish_mathur · Answer

Hi,
Please show the exact result that you are expecting.

anonymous · Answer

Hi xue,Thank you for the reply. The given date is nothing but the same date in the table. But what I want is that for each date in table, it should check if it falls in 30 days or 60 days or 90 days. So the measure that you added should be ideally 3 different measures.Pls advise with this filter.

Forum Discussion

Count based on multiple detail rows

3 Replies

Related content

Counting nulls

count values from multiple columns

Count across multiple columns

Count from multiple columns

Missing Connection Details