Distinct Count on ID between periods

[Delmekka]

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 

[v-yangliu-msft]

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]

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

[Delmekka]

@v-yangliu-msft 

@amitchandak 

@Ashish_Mathur 

@SpartaBI 

If anyone could help me please that would be so much appreciated 🙂 

Thanks a lot

[Ashish_Mathur]

Hi,

Share some data and show the expected result.  If you share the download link of your file, then create a Calendar Table and ensure you have a Weekday/Weeknum column there.