Forum Discussion

Delmekka's avatar
Delmekka
Frequent Visitor
4 years ago

Distinct Count on ID between periods

Hi there, 

 

So glad i joined this community and prior of everything, thanks for the helping hand 🙂 

I have this specific question, which in my mind sound like and "IF" situation.

 

I'm managing a team of Reps, they visit companies from Monday to Friday. 

I'd like to do a distinct count on a weekly basis on the Companies ID. 

 

For example : 

When a rep visits a company that has not been seen more than 1 time this week, the value of the contact is equal 1. 

On the other hand, when a rep is visiting a same company during one week, it's equal : 1/(number of visit), if it's 2 visits it's equal 0,5 and so on. 

 

The tricky situation i am in is defining in the measure in terms of Time Intelligence. 

Right now, my guess would go with an IF measure and a DistinctCount but i'm not so good in DAX :'(. 

 

If you have any tips for me that would be much appreciated 🙂 

Thanks a lot, have a nice summer and stay safe 

Need Help
Tips and Tricks

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Delmekka ,

    I created some data:

    Here are the steps you can follow：

    1. Create measure.

    solve1_measure =
var _count=
COUNTX(
    FILTER(ALL('Table'),'Table'[Weeknum]=MAX('Table'[Weeknum])&& NOT('Table'[Week]) in {6,7}&&'Table'[visits]="Yes"),[Date])
return
IF(
    _count >1,1,0)
    solve2_measure =
var _count=
COUNTX(FILTER(ALL('Table'),'Table'[Weeknum]=MAX('Table'[Weeknum])&&NOT( 'Table'[Week]) in {6,7}&&'Table'[company]=MAX('Table'[company])&&
'Table'[visits]="YES"),[Date])
return
DIVIDE(1,_count)

    2. Result:

    If I have misunderstood your meaning, please provide your desired output.

     

    Best Regards,

    Liu Yang

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

    • Delmekka's avatar
      Delmekka
      Frequent Visitor
      4 years ago

      Hi there, 

       

      Thanks for the answer and to be honest it kinda helped me understanding the situation i am in. 

      First of all, since i'm working on CRM data, every line i have is equal to a visit so i don't have a use of boolean type of data like the YES/NO visit. 

       

      Your method for the 2 solve proposition is actually quite what i'm looking for. 

      But in this situation where i used your answer, i only get one result which is 1.

       

      Here you can see my structure, 

      I'm counting the number of IDs (Account_vod_c) on a weekly basis. 

      So this better be a CountX based on the 1st and last day of the week, and on the same week (So WeekNum and Weekday are good for this).

       

      My only concern is that i keep getting the same value over and over.

      Thanks a lot for the help

       

      Driss