Desktop

[analystict]

Hey there,

What I am trying to do is to measure the amount of sales that are generated from leads which were created in a specific timeframe AND converted in the specified timeframe (filtered by date from a date dimension).  

This way I can see how many sales belong to the specified timeframe's leads and how many sales are "late" sales.

Since using two active relationships at a time in the same tables is not possible, I tried the following workaround:

First, I have this measure which works perfectly fine and takes the date that the lead was converted (converted_on):

Conversions_WithLate =
    CALCULATE(
        COUNTA('Lead'[id]),
        USERELATIONSHIP(DateTable[Date], 'Lead'[converted_on])
    )
    
RETURN Total

Then what I did as an attempted workaround, is to create another measure like so:

Conversions_NoLate = 
    CALCULATE(
        [Conversions_WithLate]
        USERELATIONSHIP(DateTable[Date], 'Lead'[created_on])
    )

Unfortunately, this approach did not work and I'm quite stuck. How do I go about this?

[AlB · Accepted solution]

Hi @analystict

Please always show the structure of the tables in your data model and their relationships. Otherwise people trying to answer have to start making guesses. Share the sample data in text-tabular format so that it can be readily copied.

From what I can understand, you could try the following:

Measure =
VAR LeadsConvertedInPeriod =
    CALCULATETABLE (
        DISTINCT ( 'Lead'[id] ),
        USERELATIONSHIP ( DateTable[Date], 'Lead'[converted_on] )
    )
VAR LeadsCreatedInPeriod =
    CALCULATETABLE (
        DISTINCT ( 'Lead'[id] ),
        USERELATIONSHIP ( DateTable[Date], 'Lead'[created_on] )
    )
VAR LeadsCreatedAndConvertedInPeriod =
    INTERSECT ( LeadsCreatedInPeriod, LeadsConvertedInPeriod )
RETURN
    COUNTROWS ( LeadsCreatedAndConvertedInPeriod )

[AlB · Accepted solution]

Hi @analystict

Please always show the structure of the tables in your data model and their relationships. Otherwise people trying to answer have to start making guesses. Share the sample data in text-tabular format so that it can be readily copied.

From what I can understand, you could try the following:

Measure =
VAR LeadsConvertedInPeriod =
    CALCULATETABLE (
        DISTINCT ( 'Lead'[id] ),
        USERELATIONSHIP ( DateTable[Date], 'Lead'[converted_on] )
    )
VAR LeadsCreatedInPeriod =
    CALCULATETABLE (
        DISTINCT ( 'Lead'[id] ),
        USERELATIONSHIP ( DateTable[Date], 'Lead'[created_on] )
    )
VAR LeadsCreatedAndConvertedInPeriod =
    INTERSECT ( LeadsCreatedInPeriod, LeadsConvertedInPeriod )
RETURN
    COUNTROWS ( LeadsCreatedAndConvertedInPeriod )

[analystict]

Hey @AlB,

Sorry about that! I usually do show the structure - slipped out of my mind this time trying to make this as clear as possible 

This works great though - it is exactly what I needed. Thank you!