cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Count number of tests

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
1 ACCEPTED SOLUTION
Community Support

Hi @brettg ,

Firstly, create a table for "Count":

``New Table = {1,2,3}``

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

6 REPLIES 6
Community Support

Hi @brettg ,

Firstly, create a table for "Count":

``New Table = {1,2,3}``

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

Helper II

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

Helper II

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

Helper II

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.

Super User

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

Proud to be a Super User!

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

Proud to be a Super User!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors