Show max values of measure in hierarchy

[MarcinJan]

Hi All

I have a usage table:

Date	UserColumn	ComponentColumn
1:00 01/03/2021	User1	Component1
1:00 01/03/2021	User2	Component1
1:00 01/03/2021	User3	Component1
1:05 01/03/2021	User1	Component1
1:05 01/03/2021	User4	Component1
1:05 01/03/2021	User5	Component1
1:05 01/03/2021	User6	Component1
2:00 01/03/2021	User7	Component1

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;

But I wish to see a matrix, where I can see the distinct count on minute hierarchy level, wheras on hours and days hierarchy the max of minutes hierarchy. So:

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 - 4 distinct users;

Day 1 - 4 distinct users;

Now you see the difference ? on Hour and day level ?

I tried  several measures, GROUPBY, FILTERING but nothing works. I have to also filter Components. DISTINCTCOUNTS always adjusts to the hierarchy level and I did not fiugure out how to pass the value using MAXX. Any ideas how should I approach it ?

[Jihwan_Kim · Accepted solution]

Hi, @MarcinJan 

Please kindly check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

DistinctCount Users max of minute hierarchy =
VAR newtable =
SUMMARIZE (
ALL ( Times ),
Times[Minute Label],
"@distinctcount", DISTINCTCOUNT ( Data[UserColumn] )
)
RETURN
IF (
ISBLANK ( DISTINCTCOUNT ( Data[UserColumn] ) ),
BLANK (),
IF (
ISFILTERED ( Times[Minute Label] ),
DISTINCTCOUNT ( Data[UserColumn] ),
MAXX ( newtable, [@distinctcount] )
)
)

https://www.dropbox.com/s/i5ciuyw0kbldh5d/marcinjan.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[MarcinJan · Accepted solution]

Jihwan Kim,

When used it with real data, the amounts do not look correct even the proper relationships are in place. Distinct Count is your measue, Count of User Column is distinct count of users. This 17 seems to count entire TIme span..

[MarcinJan]

I eventually managed to use the blend of 2 replies above. The temp table really increases the performance of the app.

[Jihwan_Kim · Accepted solution]

Hi, @MarcinJan 

Please kindly check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

DistinctCount Users max of minute hierarchy =
VAR newtable =
SUMMARIZE (
ALL ( Times ),
Times[Minute Label],
"@distinctcount", DISTINCTCOUNT ( Data[UserColumn] )
)
RETURN
IF (
ISBLANK ( DISTINCTCOUNT ( Data[UserColumn] ) ),
BLANK (),
IF (
ISFILTERED ( Times[Minute Label] ),
DISTINCTCOUNT ( Data[UserColumn] ),
MAXX ( newtable, [@distinctcount] )
)
)

https://www.dropbox.com/s/i5ciuyw0kbldh5d/marcinjan.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[MarcinJan · Accepted solution]

Jihwan Kim,

When used it with real data, the amounts do not look correct even the proper relationships are in place. Distinct Count is your measue, Count of User Column is distinct count of users. This 17 seems to count entire TIme span..

[v-kkf-msft · Accepted solution]

Hi @MarcinJan ,

Try the following formula:

Measure = 
MAXX(
    SUMMARIZE(
        'Table',
        'Table'[ComponentColumn],
        'Table'[Date/time],
        "_count",
        DISTINCTCOUNT('Table'[UserColumn])
    ),
    [_count]
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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