none
measure to calculate users who logged in from daily snapshot data RRS feed

  • Question

  • Hi,

    I have a model with Date dim and fact table containing username,last_login,application columns.Fulldate is related to last_login.Last_login is the max(logindate) for each user per row in fact table per day.My measure should calculate count users who "didnt login" in last 7 days.I tried below to get the count to get users who used and count is correct but when i drag last_login in the visual along with this measure,it shows me all dates the user logged in last 7 days.

    Pls help.The attachment has 2 images:

    Expected one gives me correct number and visual gives me correct data because the calculated table i have does below:

    Table =
    SUMMARIZE (
    Test_AppsUsage_F,
    Test_AppsUsage_F[Username],
    Test_AppsUsage_F[Subsystem],
    "Max_logon", LASTDATE ( Test_AppsUsage_F[Last_accessed] )

    )

    Used(Measure)=calculate(DISTINCTCOUNT('Table'[Username]),'Table'[Max_logon]>today()-7)

    Notused=DISTINCTCOUNT('Table'[Username])-[Measure]

    I tried the same logic in a measure within the fact table because i shouldnt be using a calculated table for this.

    The measure Used_NEW gives me correct Total count but the visual brings in additional rows for all last_accessed dates per user.How do i fix this:

    #Used_NEW =
    CALCULATE(DISTINCTCOUNT(Test_AppsUsage_F[Username]),
    FILTER (
    SUMMARIZE (
    Test_AppsUsage_F,
    Test_AppsUsage_F[Username],
    Test_AppsUsage_F[Subsystem],
    "Max_logon", LASTDATE ( Test_AppsUsage_F[Last_accessed] )
    ),
    LASTDATE ( Test_AppsUsage_F[Last_accessed] )
    > TODAY () - 7
    )

    )

    This #NotUsed_NEW gives me wrong count altogther: #NotUsed_NEW=[#TotalUsers]-[#Used]

    Pls fix the 2 measures.

    I have attached the image of the expected(left) which comes from calculated table and output

    from fact on right side.

    Saturday, August 10, 2019 4:06 AM

Answers

  • Hi msdnpublic1234,

    Thanks for your post.

    Per your description, you don't have to add measures as calculated columns to the table. What you're doing is not measure, it is calculated column. You could create measures under table "Test_AppsUsage_F" directly.

    [Measure Used]=calculate(DISTINCTCOUNT('Test_AppsUsage_F'[Username]),'Test_AppsUsage_F'[Last_accessed]>today()-7)
    
    [Measure ALL]=calculate(DISTINCTCOUNT('Test_AppsUsage_F'[Username]))
    
    [Measure Not Used]=[Measure ALL]-[Measure Used]

    Reference

    Create and use your own measures

    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.

    • Marked as answer by msdnpublic1234 Thursday, August 15, 2019 8:22 PM
    Monday, August 12, 2019 3:29 AM

All replies

  • Hi msdnpublic1234,

    Thanks for your post.

    Per your description, you don't have to add measures as calculated columns to the table. What you're doing is not measure, it is calculated column. You could create measures under table "Test_AppsUsage_F" directly.

    [Measure Used]=calculate(DISTINCTCOUNT('Test_AppsUsage_F'[Username]),'Test_AppsUsage_F'[Last_accessed]>today()-7)
    
    [Measure ALL]=calculate(DISTINCTCOUNT('Test_AppsUsage_F'[Username]))
    
    [Measure Not Used]=[Measure ALL]-[Measure Used]

    Reference

    Create and use your own measures

    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.

    • Marked as answer by msdnpublic1234 Thursday, August 15, 2019 8:22 PM
    Monday, August 12, 2019 3:29 AM
  • Thanks Will.IS it possible to select a variable N value and pass it as parameter to these measures to see data for last N days,not just 7 days.

    Also,is it possible to see the trend of over last 7 days or months as to how many users did not use system.

    Tuesday, August 13, 2019 12:17 AM
  • Hi msdnpublic1234,

    Here are the answers for your questions.

    >>IS it possible to select a variable N value and pass it as parameter to these measures to see data for >>last N days, not just 7 days.

    You could consider using What if parameter to visualize variables in Power BI Desktop. Whether it suits your case or not depends on your scenario.

    Create and use a What if parameter to visualize variables in Power BI Desktop

    For detailed implementation, you could submit your doubts to Power Bi forum via https://community.powerbi.com/t5/Forums/ct-p/PBI_Comm_Forums

    >>Also, is it possible to see the trend of over last 7 days or months as to how many users did not use >>system.

    This belongs to the business requirements, you need to make a more detailed description about your design. If your develop tool is POWER BI desktop, you could submit it to Power Bi forum directly.

    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.

    Thursday, August 15, 2019 5:54 AM
  • One question her: the measure works fine except one thing when i browse the model.When i try to see who arer the users who have NOT ACCESSED in last 7 days with the NotUsed measure,i see 1/0 corresponding to the count.Is it not possible to just see the usernames who have not used as 1 and suppress the 0's:

    Wednesday, August 28, 2019 5:47 PM