Compare the values in a column by row group

[jcecil2]

Hello, 

I am attempting to find the number of members gained and members lost by hour for each group in the table below:

DATE	HOUR	GROUP	MEMBERS
2018-01-01	0	1	A, B
2018-01-01	1	1	A, C
2018-01-01	2	1	A, D, E
2018-01-01	3	1	D, F
2018-01-01	0	2	A
2018-01-01	1	2	C
2018-01-01	2	2	C, D
2018-01-01	3	2	D, E, F

My output would be:

DATE	HOUR	GROUP	MEMBERS	MEMBERS GAINED	MEMBERS LOST
2018-01-01	0	1	A, B	NULL	NULL
2018-01-01	1	1	A, C	1	1
2018-01-01	2	1	A, D, E	2	1
2018-01-01	3	1	D, F	1	2
2018-01-01	0	2	A	NULL	NULL
2018-01-01	1	2	C	1	1
2018-01-01	2	2	C, D	1	0
2018-01-01	3	2	D, E, F	2	1

I'm not entirely sure this is possible in DAX, as it would require parsing the string in the members column. However, I could have the table set up to where each member has their own row. The table would look like this:

DATE	HOUR	GROUP	MEMBER
2018-01-01	0	1	A
2018-01-01	0	1	B
2018-01-01	1	1	A
2018-01-01	1	1	C
2018-01-01	2	1	A
2018-01-01	2	1	D
2018-01-01	2	1	E

I am not sure the best way to approach this problem. Any suggestions would be greatly appreciated. 

Thank you,

[sturlaws]

Hi, @jcecil2,

I can't see any ways of solving this with the members concatenated into a string, although it might be possible in power query(but outside of what I'm capable of doing). When there is a row for each member, there are a couple of possibilities.

If you can do this by columns, meaning it does not have to be dynamic with respect to filtering in a report, you can use something like this to calculate lost members with dax:

Lost =
VAR _group =
    CALCULATE ( SELECTEDVALUE ( 'Table'[GROUP] ) )
VAR _hour =
    CALCULATE ( SELECTEDVALUE ( 'Table'[HOUR] ) )
VAR _date =
    IF ( _hour = 24; DATEADD ( 'Table'[DATE]; 1; DAY ); 'Table'[DATE] )
VAR _hourNext =
    IF ( _hour = 24; 0; _hour + 1 )
VAR _member =
    CALCULATE ( SELECTEDVALUE ( 'Table'[MEMBERS] ) )
RETURN
    IF (
        COUNTROWS (
            FILTER (
                ALL ( 'Table' );
                'Table'[MEMBERS] = _member
                    && 'Table'[GROUP] = _group
                    && _hourNext = 'Table'[HOUR]
            )
        ) < 1;
        1;
        0
    )

and corresponding for the gained members. Note that the lost members are calculated looking forward in time, while gained members are calculated looking backwards in time. This means that they are shifted 1 hour, and you will have to come up with some tricks if you want to show it like your desired outcome.

Something similar can also be handled by Power Query. Not that many steps to it, but I'll share a file instead of describing the steps: .pbix 

For both options, the begining and the end of the dataset have to be handled. You can handle it dynamically or you can hardcode it.

If the input data has one row for each member, how should it be displayed? With all members concatenated into 1 string as before? This will have to be handled in a different table, or keeping a column with those values.

Now, if you have to do this dynamically and responsive to filters and slicers, you have to do it as a measure. Then you should take a look at the new and returning customer pattern over at daxpatterns.com: https://www.daxpatterns.com/new-and-returning-customers/

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.