Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Measure - distinct dates per user - filter not working

Goodday all

 

To measure User activity from Dynamics CRM in PowerBI I thought of the following way:
I would like to see how many days a month a user has done something in CRM (access, update, create, delete etc).

So if a user has 4 activities on january 1st and 1 on january 2nd, I want this to reflect as 2 days (so not counting the activities but only the distinct days he/she has done something)


We have a table called 'Audits' in here every change a user makes is recorded.

 

So I thought I would write a measure counting distinct values of the audit created on field and then in the report I would put the created on months are rows and sales managers as columns to show the value per month, per sales manager

 

But this unfortunately does not work. It is showing the same value for every sales manager and showing all days of the month, not just the ones where a sales manager actually has an audit record.

 

What am I doing wrong?

 

distinct created on = COUNTROWS(DISTINCT(audits[createdon].[Date]))
is my formula
 
Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    I think Audit is not joined with Sales manager table that is what causes this. Can check data model and see, is it connected and can be connected.

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    Could you please show me your sample data and model view? Do mask sensitive data before uploading. Thanks.

     

    Best Regards

    Allan

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       

      In the audits table I have UserIdValue, I added a calculated column with a lookup to the systemuser table to get the sales managers' name.

      So indeed there is no relationship but in this way I did get the sales managers' name in the audits table.

       

      I will try to post a screenshot in my next post

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

         

        The all user - activity table you see below only does a count of all user activity with a simple measure with a countrows of the audit table.  But I am not sure why there is a separate table for it to be honest... 

        perhaps I should use that table to perform the distinct count of audits I want to see as well?

        But I would like to understand why if that is the case.