Count number of tests

[brettg]

Hello,

I would like to make a table which shows me users with 1 test, 2 tests, 3 tests etc. Something like the below based on the data set.

I know I could do this using "group by", but then I wouldn't be able to look at historic data. Is there a way to do this with a measure that would consider the date slicers on the page? (If I change date slicers, only tests in that period are counted)

Test Count	Number of users
1	1
2	2
3	1

UserID	Gender	Age	TestResult	TestDateTime
006	Male	34	PASS	2022-02-01 19:50:10.897
001	Female	29	PASS	2022-01-31 18:35:25.830
004	Male	25	FAIL	2022-01-30 10:50:48.873
001	Female	29	PASS	2022-01-28 11:39:51.737
001	Female	29	FAIL	2022-01-27 11:49:38.180
002	Female	36	PASS	2022-01-26 15:07:48.967
004	Male	25	PASS	2022-01-25 14:56:13.130
006	Male	34	PASS	2022-01-24 17:41:15.973

[v-eqin-msft · Accepted solution]

Hi @brettg ,

Firstly, create a table for "Count":

New Table = {1,2,3}

Please try:

Count = 
CALCULATE(COUNTROWS('Table'),FILTER(ALLSELECTED('Table'),[UserID]=MAX('Table'[UserID]) &&  [TestDateTime]>=MIN('Table'[TestDateTime]) && [TestDateTime]<=MAX('Table'[TestDateTime])))

Count(again based on meassure) = 
var _t=SUMMARIZE('Table','Table'[UserID],"Count by UserID",[Count])
return COUNTROWS(FILTER(_t,[Count by UserID]=MAX('New Table'[Test])))+0

Output:

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

[brettg]

Hi Eyelyn,

This is perfect thank you! Just what I was looking for.

Where you've done {1,2,3} on the new table, would it be simple for me to go up to 10 and then include "10 or more"? 

Kind regards,

Brett

[brettg]

Not quite what I'm looking for I'm afraid! SelectedValue won't work when my date slicer is between two values.

I think I need two measures? one to count the distinct UserID's and another to group them

[brettg]

Hello,

I need the measure to do a distinct count for "UserID" from the second table. 

Then I'd like to create the first table by grouping the user counts into repeat test usage.

[mh2587]

Measure = CALCULATE(distinctCount(Table[UserId]),selectedvalue( Table[date]))

[mh2587]

Measure = CALCULATE(Count(Table[Test]),selectedvalue( Table[date]))