DISTINCT COUNT Grouped in BINs Filtered Dynamically

[Anonymous]

Good morning!

Could I ask if any one of you can solve this model:

I have read a number of post before and asked help from which I learned how to:
- Do bins in a graph with a bins table

- DISTINCTCOUNT() my users to get the number of entries per day / period using a measure 

The Challange:

- Filter both the User & Login tables dynamically (with a slicer) so that I can show the aggregated logins of users that were active during the specified time. That is, users that were Licensed but had zero logins should appear in the graph under the "0 logins" bin. Just showing users that > 0 logins in period does not cut it.  

It is possible to build a measure on the DISTINCTCOUNT() measure (which specifies the number of logins in the given period per user) which groups the counts of users, that are "Licensed", during the given period? 

Thank you inadvance!

[v-juanli-msft · Accepted solution]

Hello @ThomasBart

First create a column in the table "User login registration"

date = [login datetime]. [Date]

Create a relationship between this table and the "date" table based on this new "date" column created.

Next, create a measure in the table "User Login Registration"

count of record = CALCULATE(DISTINCTCOUNT('Usre login record'[date]),FILTER(ALLSELECTED('system users'),'system users'[Unique Id]=MAX('system users'[Unique Id])))

Next, create a range table as the links you provide.

Create a measure

count range =
CALCULATE (
    DISTINCTCOUNT ( 'system users'[Unique Id] ),
    FILTER (
        VALUES ( 'system users' ),
        COUNTROWS (
            FILTER (
                'range table',
                'range table'[min] <= [count of record]
                    && 'range table'[max] >= [count of record]
            )
        ) > 0
            && 'system users'[user status] = "Licensed"
    )
)

Add "range" and this measurement to the column chart.

Best regards
Maggie
Community Support Team _ Maggie Li
If this post helps, please consider accepting it as the solution to help other members find it more quickly.

[Anonymous]

Hello Maggie,

This is a really sick piece of help, thank you so much the key was the "..FILTER(ALLSELECTED(..." part of the formula.

I am veyr greatfull!

Thomas