none
Inactive running count day over day RRS feed

  • Question

  • Hi,

    Below is link to sample data.I basically want to know how many users stayed inactive end of each month or week.Pls suggest best method to have this keeping in mind the ones inactive have "null" in lastlogin date column.

    https://drive.google.com/file/d/1ripLoGNkbKOZvUf3oZtgtEBMNafQF7jP/view?usp=sharing

    I would like to have cumulative count of users who are inactive each day or end of each week or end of month.I tried the below but it doesnt give me the output and query times out.Pls help.Attached is the sample data and expected output.

    Note: Last_logon is related to Date (active). Snaphotdate and Date are related as inactive.

    for this requirement,i had to use inactive because of null logon dates for those inactive users.

    My meaure:

    CALCULATE (

    COUNTROWS( Dim ),Dim[Activity]="Inactive",USERELATIONSHIP ( Date[Date], Dim[SnapshotDate] ),FILTER(allselected(Date),Date[Date]<max(Dim[Snapshotdate])))

    This measure is giving me correct running counts by Day as you see above.

    The issue is i am not able to see users data when i bring in username,system column into column chart,can you please point out how to fix this.:

    Attached is the image of sample data and desired result.






    Monday, August 12, 2019 2:55 AM

All replies

  • Hi msdnpublic1234,

    Thanks for your post.

    Per your description, what you show is grand total at the last line, not cumulative count of users. Besides, I am not clear about what sample data of table "Dim" is like, what is the final destination of your requirement. Could you please make a complete description about this? It sounds like you have shown us part information.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 12, 2019 6:16 AM
  • Will,

    I have provided the image of what i want and sample data on the original question.Pls assist.

    Monday, August 12, 2019 11:45 AM
  • Will,

    I have provided the image of what i want and sample data on the original question.Pls assist.

    Hi msdnpublic1234,

    Here are steps for your reference.

    Step 1 Create a calculated table "TableExpected"

    TableExpected = 'Date'

    Step 2 create a calculated column "count Of User " on that calculated table.

    count Of User = CALCULATE(count('Fact'[username]),filter('Fact', 'Fact'[operation]="Inactive" && 'Fact'[SnapshotDate]=TableExpected[Date]))-CALCULATE(count('Fact'[username]),filter('Fact', 'Fact'[operation]="Active" && 'Fact'[SnapshotDate]=TableExpected[Date]))

    Step 3 create a calculated column "cumulative count of users" on that calculated table.

    cumulative count of users = CALCULATE(sum(TableExpected[count Of User]),FILTER(TableExpected,TableExpected[Date]<=EARLIER(TableExpected[Date])))

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 13, 2019 7:29 AM
  • Thanks for your reply.Have following question:

    • But is it necessary to create a calculated table besides Fact and Date tables?Is it not possible to achieve that as a measure within Fact by using snapshotdate and date joined.
    •  can I slice by date table if i go with your solution?
    • I did'nt understand what it means by TableExpected='Date' as i already have Date table as per my sample model below:

    https://drive.google.com/file/d/1ripLoGNkbKOZvUf3oZtgtEBMNafQF7jP/view?usp=sharing

    I assume i can  create all these columns in Date table itself.

    • I tried to replicate what you suggest ,it gives the count per day as expected,but when i slice by month or any other date field,i get wrong output.When slice by month,i get 24 a sum of all the counts,but in reality the number of users inactive in August so far would be 3.

    Pls suggest best approach.

    Tuesday, August 13, 2019 10:52 AM
  • Hi msdnpublic1234,

    Thanks for your reply.

    Here are the answers for your questions.

    >>But is it necessary to create a calculated table besides Fact and Date tables? Is it not possible to >>achieve that as a measure within Fact by using snapshotdate and date joined

    Whether create a calculated table or not depends on your hobby, but it doesn't affect that you define measures.

    >>can I slice by date table if i go with your solution?

    Yes, you can. Just make a relationship between the calculated table and table "Date".

    >>I did'nt understand what it means by TableExpected='Date' as i already have Date table as per my sample model below:

    I just put all measures into calculated table to display expected results.

    >>I tried to replicate what you suggest ,it gives the count per day as expected,but when i slice >>by month or any other date field,i get wrong output.When slice by month,i get 24 a sum of all >>the counts,but in reality the number of users inactive in August so far would be 3.

    It depends on your design. In my opinion, you need to create a measure like the DAX expression of step 3 in my previous reply. My calculated table is just for your reference, you could use same DAX expression to create measures for other columns.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 14, 2019 9:35 AM