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 managin...
Need Help
Tips and Tricks
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

     

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        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.