Count users that use all components of product

[MarcinJan]

Hi All,

I have a usage log of components:

Start Date	End Date	User Column	Component Column
15:05 02/03/2021	15:20 02/03/2021	User1	Component1
15:10 02/03/2021	18:05 02/03/2021	User1	Component2
10:05 03/03/2021	15:05 05/03/2021	User2	Component3
08:05 04/03/2021	15:05 10/03/2021	User3	Component4

Now, components are assigned to products in a table, note that one component can exist in many products:

Product Column	Component Column
Product1	Component1
Product1	Component2
Product2	Component1
Product2	Component3

Therefore there is many to many relationship between Component Column in log and Products table.

There is a Date table that is not linked to anything, it just display each day and 5 minutes interval in the day (the shortest logged period)

I am looking for number of users that used ALL components of filtered Product. So far I found a way to filter ANY OF. I use 2 measures:

Measure Calculated Usage:

Calculated Usage =

VAR MinDate =

MIN ( Date[DateTime] )

VAR MaxDate =

MAX ( Date[DateTime] )

RETURN

CALCULATE (

DISTINCTCOUNT( 'Usage Runs (Excel)'[User] ),

'Usage Runs (Excel)','Usage Runs (Excel)'[Start Date] <= MinDate,

'Usage Runs (Excel)', 'Usage Runs (Excel)'[End Date] > MaxDate

)

Measure Usage Peak to find a max value in a given period:

Usage Peak =

MAXX ( DaysHoursMinutes, [Calculated Usage] )

And the matrix:

I was trying to use filters but I always end up with users count that use ANY OF the component, not ALL of them.

Anyone able to help ?

[lbendlin]

Can you maybe provide a little bit more of sample data to cover all anticipated scenarios?

[MarcinJan]

So, consider:

Day 1, Hour 1, Minute 00 - User1, User2, User3;

Day 1, Hour 1, Minute 05 - User1, User4, User5, User6;

Day 1, Hour 2, Minute 00 - User7;

That gives us:

Day 1, Hour 1, Minute 00 - 3 distinct users;

Day 1, Hour 1, Minute 05 - 4 distinct users;

Day 1, Hour 2, Minute 00 - 1 distinct user;

Day 1, Hour 1 - 6 distinct users;

Day 1 - 7 distinct users;

So you see, that my current method calculates distinct users based on the time interval. But when in the report I drill anything above minutes, I want to see the largest value of minutes level (in this case 4) not 7.

[lbendlin]

use the Cinderella approach - SELECTCOLUMNS or SUMMARIZE etc - to make them the same format.

[MarcinJan]

Ok, still trying to refine steps one and two.

I have a matrix as below, in this case its 01/02/2021, hour 3 and each 5 minutes of this hour. The number you can see are distinct users using each component at specific time, but total does not show a correct value - I want it to show the highest value of each 5 minutes, not a sum of all distinct users. Any ideas how to alter the total ? In this case I want Component1 Total to show 7; Component2 Total to show 6.

This works but only to the minute drilldown. I want the value to be "forwarded" to Hour and Day hierarchy, not recalculated at each level.

Measure 11 =

MAXX (

'Table',

DISTINCTCOUNT( 'Table'[User] )

//ALLEXCEPT ( 'Table', 'Table'[Minute] )

)

Thanks!!

[lbendlin]

Your final outcome for this measure is a scalar value, but all the intermediate steps are table variables.

[MarcinJan]

Ok, I think I sorted out the 2 first steps (I created a new table based on a same principle as ticketing count system)

How about intersection - I can't use INTERSECT as Product-Component table has different columns then my Component-Time-User table.

[lbendlin]

Approach the issue methodically

- for each time interval find all users that were active

- for each user find the components they were using

- intersect the list of components with your components-products table to see how many products are completely covered.

Use variables and CONCATENATEX along the way to make sure you get the right intermediate results.

[MarcinJan]

That sounds reasonable, but I am struggling with the first step already:

- should that be a table ? with extra columns, or can I use measures ? 

- wondering about intervals.. should I use just one timestamp to slice through the usage logs ?

I am using ADDCOLUMNS and FILTER to create a table like this but constantly receiving error "multiple columns cannot be converted to a scalar value"

Any thoughts here ?

Interval Start Date  |   Interval End Date  |  User list 

03/03/2021 05:10   |  03/03/2021 05:15  | User1, User2