Identifier unique or not, per row, powerpivot

[decarsul]

Good day all,

Hopefully this post is saved  . . .

In the past, i've visitted these forums and got an answer to my question, so i'm having hopes!

I have a dataset in which i want to add a column to see if a customer id is unique or not per row.

This so i can count how many unique customers have called and to create an average on how many customers call multiple times within a time period of 5 days.

The datatable i have:

call id	contact date	customerid
1	1-7-2020	100
2	2-7-2020	102
3	8-7-2020	100
4	2-7-2020	104
5	20-7-2020	120
6	21-7-2020	180
7	22-7-2020	180
8	31-7-2020	258
9	28-7-2020	6524
10	23-7-2020	619663

The result i need:

call id	contact date	customerid	Repeat 5 days
1	1-7-2020	100	0
2	2-7-2020	102	0
3	8-7-2020	100	0
4	2-7-2020	104	0
5	20-7-2020	120	0
6	21-7-2020	180	1
7	22-7-2020	180	1
8	31-7-2020	258	0
9	28-7-2020	6524	0
10	23-7-2020	619663	0

I have tried the following code, which did not work:

Calculate(distinctcount(callid);groupby(table;customerid))

Alternatively i just want a value true or false per row, to see if that customer has called multipletimes within the time period.

The timeperiod filter has not been added to above tried formula, as i was figuring out the first part and i could just add an if statement to it to make it a true/false value.

Hoping you guys have some suggestions!

p.s. i'm hoping to avoid creating an facts table with unique customer id's and a count of callid's

[Icey · Accepted solution]

Hi @decarsul ,

How about creating a measure like so:

Measure =
VAR thisdate =
    MAX ( 'Table'[contact date] )
RETURN
    CALCULATE (
        COUNT ( 'Table'[call id] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[customerid] = MAX ( 'Table'[customerid] )
                && 'Table'[contact date] >= thisdate - 4
                && 'Table'[contact date] <= MAX ( 'Table'[contact date] )
        )
    )

Then, you can find that if [Measure]>1, it means that this "customerid" has called once in previous 5 days.

And you can also create the measure like so:

Is Repeated in previous 5 days = 
VAR thisdate =
    MAX ( 'Table'[contact date] )
VAR Count_ =
    CALCULATE (
        COUNT ( 'Table'[call id] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[customerid] = MAX ( 'Table'[customerid] )
                && 'Table'[contact date] >= thisdate - 4
                && 'Table'[contact date] <= MAX ( 'Table'[contact date] )
        )
    )
RETURN
    IF ( Count_ > 1, "True", "False" )

Best Regards,

Icey

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

[decarsul]

Great suggestion, let me tinker with that.

This will however cause me not to be able to use this in an historical sence.

As in, i won't be able to plot a count on the calendar dimension i'm planning to use in the future.

p.s. and not related, any idea why i keep getting notifications in spanish?

[Icey]

Hi @decarsul ,

If you want to use a calendar dimension, try this:

Is Repeated in previous 5 days 2 =
VAR thisdate =
    MAX ( 'Calendar'[Date] )
VAR Customer_ =
    MAX ( 'Table'[customerid] )
VAR Count_ =
    CALCULATE (
        COUNT ( 'Table'[call id] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[customerid] = Customer_
                && 'Table'[contact date] >= thisdate - 4
                && 'Table'[contact date] <= thisdate
        )
    )
RETURN
    IF ( Count_ > 1, "True", "False" )

In addition, I don't know how to disable Spanish notifications. I meet the issue, too.☹️

Best Regards,

Icey

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

[decarsul]

Allright,

Me and colleague did some tinkering, haven't looked at the 2nd option yet.

We used the first code, than we added a calculated column IF(is repeated=1;1;0) 

This generates a correct colum with true or falses which i can then use to plot on calendar and the likes.

Will check additional code to see if we can enhance performance, but in essence issue is solved. thanks!

[decarsul]

As a reference.

I would write this in OBIEE as follows:

Count(distinct 'table'.'callid' by 'table'.'customerid')