Filtering a measure? As in, if result > 1, then show it, otherwise no

[grggmrtn]

I'm going blind staring at my data and trying to figure out how to make what SHOULD be a simple measure work - I'm hoping someone can help me

My data looks something like this 'Services' table:

PersonID	Service	ServiceID	Date
1		One	123		01-01-2019
1		One	123		02-01-2019
1		One	123		03-01-2019
1		Two	234		01-02-2019
1		Two	234		02-02-2019
1		Two	234		03-02-2019
1		Two	234		04-02-2019
2		One	345		01-03-2019
2		One	345		02-03-2019
2		One	345		03-03-2019
2		One	345		04-03-2019
2		Two	456		05-03-2019
2		Two	456		06-03-2019
2		Two	456		07-03-2019
3		One	567		01-04-2019
3		One	567		02-04-2019
3		One	567		03-04-2019
3		One	678		01-05-2019
3		One	678		02-05-2019
3		One	678		03-05-2019
3		One	678		04-05-2019
3		One	678		05-05-2019

notice that each service has a different ID when a new date period is begun.

I have found the [StartDate] creating a new column:

CALCULATE (
    MIN ( Services[Date] );
    ALL ( Services);
    Services[Service ID] = EARLIER ( Services[Service ID] )

The measure I need to create needs to give me the number of [PersonID] that have more than one [StartDate], per Service.

So the result for above would be 1, since only person 3 has the same service with two [StartDate]

I thought I was on the right track creating the following measure:

Number of return customers = 
VAR servicestarts =
CALCULATE(
    DISTINCTCOUNT(Services[PersonID]);
    FILTER(ALLSELECTED(Services);Services[Service]=MAX(Services[Service])))
RETURN
IF(servicestarts > 0; servicestarts )

but it seems to be way off, and I can't seem to figure out where my thinking went wrong.

Anyone?

[Zubair_Muhammad]

@grggmrtn 

Try this MEASURE

Measure =
VAR mytable =
    SUMMARIZE (
        ADDCOLUMNS ( Services, "MyMonth", MONTH ( Services[Date] ) ),
        [PersonID],
        [Service],
        [MyMonth]
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Services[PersonID] ),
        FILTER (
            ADDCOLUMNS (
                mytable,
                "myCount", COUNTROWS (
                    FILTER (
                        mytable,
                        [PersonID] = EARLIER ( [PersonID] )
                            && [Service] = EARLIER ( [Service] )
                    )
                )
            ),
            [myCount] > 1
        )
    )

[grggmrtn]

Hi @Zubair_Muhammad  thanks for your help

Unfortunately, the measure isn't working QUITE as I would need it to. From what I can see, your measure is counting the number of months that a person has had the active service. What I need is to find the number of [StartDate], in order to calculate how many service periods the person has had.

In practice, a count of the number of [ServiceID] per person, per Service would also work, in that it would give the same result.

[Zubair_Muhammad]

@grggmrtn 

I get result of 1 with above sample data which is person ID=3 which has more than 1 month for same service

What is your expected result?

[grggmrtn · Accepted solution]

Hi again @Zubair_Muhammad 

I modified your measure in the following way and now it's working as I need it to:

Number of return customers = 
VAR myTable =
    SUMMARIZE(
        Services;
        Services[ServiceID];
        Services[PersonID];
        Services[Service]
    )
RETURN
    CALCULATE(
        DISTINCTCOUNT(Services[PersonID]);
        FILTER(
            ADDCOLUMNS(
                myTable;
                "myCount"; COUNTROWS(
                    FILTER(
                        myTable;
                        Services[PersonID]=EARLIER(Services[PersonID])
                            && Services[Service]=EARLIER(Services[Service])
                    )
                )
            );
            [myCount]>1
        )
    )

My only problem is, that I'm not sure WHY it's working 🙂

Can you help me to understand your measure a bit better?

[grggmrtn]

My expected result is also 1

But the number of months for me is not necessary at all. What I need is the number of [ServiceID], per [PersonID] per [Service]

[Zubair_Muhammad]

@grggmrtn 

Please see attached file..It works with me

Please send me extended dataset it it doesnot work