Forum Discussion

luisccmm's avatar
luisccmm
Icon for Helper II rankHelper II
3 years ago
Solved

Measure that DistinctCound Leads by last_time

Hi,

I need to CREATE a MEASURE Not a Calculated Column, that DistinctCounts Leads. The problem here comes from the fact that those leads have different owners over a period of time, but I just need to count once by it last owner.
Lead_id    Owner   Date_time
XXX_1      Agent_1  01/01/23  12:00
XXX_1      Agent_2  01/01/23  12:10
XXX_2      Agent_2  01/01/23  12:11
XXX_2      Agent_3  01/01/23  12:14

Expected Result.
Owner    Measure_Distinct_Count_leads
Agent_1        0
Agent_2        1
Agent_3        1

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi luisccmm ,

     

    Please try:

    Measure =
VAR _a =
    SUMMARIZE (
        ALLSELECTED ( 'Historial de candidatos' ),
        [Lead_id],
        "Agent",
            CALCULATE (
                MAX ( 'Historial de candidatos'[Agent] ),
                FILTER (
                    'Historial de candidatos',
                    [CreatedDate] = MAX ( 'Historial de candidatos'[CreatedDate] )
                )
            )
    )
VAR _b =
    SELECTCOLUMNS ( 'Historial de candidatos', "Agent", [Agent] )
RETURN
    COUNTX ( FILTER ( _a, [Agent] IN _b ), [Lead_id] )

    Final output:

    Best Regards,

    Jianbo Li

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

8 Replies

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago

    pls try this

    DisCount = 
VAR _FilterByMaximumDate =
    FILTER (
        ADDCOLUMNS (
            'Table',
            "MaxDate",
                CALCULATE (
                    MAX ( 'Table'[Date_time] ),
                    ALLEXCEPT ( 'Table', 'Table'[Lead_id] )
                )
        ),
        [Date_time] = [MaxDate]
    )
RETURN
    SUMX ( _FilterByMaximumDate, 1 ) + 0

  • luisccmm's avatar
    luisccmm
    Icon for Helper II rankHelper II
    3 years ago

    Hi Ahmedx !! Thanks a lot for your proposal.
    Your solutions seems quite good!!

    Nevertheless I have tried it in the attached sample data and seems is not working as expected. I have created a sample data with 1K rows and events during 3 days and tried 2 experiments.
    - On the left hand side, is the Global data without datetime filter applied and the results show that I have 215 Distinct ID, nevertheless your meassure seems to count near  336. So that means it is not counting distinct Lead_id.
    - On the right side, is the same data, but with datefilter applied, and the measure shows 0 values.
    https://drive.google.com/file/d/1PlCe5j0k1ha9J3WQwaHjbHs8kmpMx3VF/view?usp=sharing  


    • Ahmedx's avatar
      Ahmedx
      Icon for Super User rankSuper User
      3 years ago

      as requested I did, now what result do you expect? pls Show results for 2 variants

      • luisccmm's avatar
        luisccmm
        Icon for Helper II rankHelper II
        3 years ago

        Hi Ahmedx 
        I have UPDATED the file, so now shows the expected Result.
        The left hand side in Green is OK, for global results, as you mentionded (I realized that the problems comes from the fact that there were some duplicated date_times so that produced duplicated results).
        The right hand side in RED, is the same concept so far but I need to be able to apply date_time range using a date_time filter. So if for exampple I pic dates from 30 Jun till 1 July, should only take into account this time range to produce the result.

        https://drive.google.com/file/d/1PlCe5j0k1ha9J3WQwaHjbHs8kmpMx3VF/view?usp=sharing