Aggregation/Filtering obstacles and how to overcome it

[Anonymous]

Hello Dear Community!

I have some data on online games. Per log-in session the customers can play for example only Tennis, or Tennis and Football or 10 different sports if they feel like it. Per log-in session the customer gets 1 row in the data.

Now what I really want is the duration of playing time filtered on first log-in per day per customer and sliced on a particular game, for example Tennis. So I created a slicer where I can select Tennis, and of course a date slicer, so far so good. 

I am not sure how to filter such that I only get the entry where min(log_in_time) grouped by customer.

I tried this measure for identifying the first session log in per customer:

1stSession =

CALCULATE(

MIN(TABLE1[log_in_time])

, VALUES(TABLE1[customer_number])

, FILTER(table1, [MEASURE_COUNT_GAMES_PER_SESSION] = 1)

)

Where the other meassure is simply reacting to the tennis selection.

And this works! Measure 1stSession gives me the exact first log in time where the customer only played the selected game in the slicer. But the problem is the other variable, duration time, or any other variable I want to add. Since I have not really filtered the data, every other column will be aggregated on all sessions alltogether instead of filtered on the first log in.

Could I simple make a measure where only the rows per customer get the value 1 if min(table(log_in_time) and set a table filter = 1? If so, how would this look? 

Thank you so much and have a nice day!

Best,

IB

[v-xiaotang]

Hi @Anonymous 

could you share your sample data and expected result of sample, so that I can write specified measure for the sample. otherwise, I doubt if I can provide right suggestion only based on your description..

Best Regards,

Community Support Team _Tang

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

[Anonymous]

Hi @v-xiaotang 

Thank you for your reply and here is some data

table1:

customer_number	login_session_number	login_date	game_name_en	duration	win/loss
1	T001	28.07.21 07:29	Tennis	20	w
1	T001	28.07.21 07:29	Soccer	20	w
1	T002	28.07.21 08:46	Tennis	10	w
1	T003	28.07.21 08:53	Tennis	5	l
2	T006	28.07.21 20:46	Soccer	5	w
2	T007	28.07.21 20:58	Tennis	40	w
2	T008	28.07.21 21:01	Soccer	5	l
3	T011	28.07.21 09:07	Tennis	25	l
3	T012	28.07.21 09:20	Soccer	5	w
3	T013	28.07.21 09:23	Tennis	25	w

table2 is a dimension table and simply a distinct of all the table1[game_name_en], I simply made this for the game_name slicer, it is joined over table1[game_name_en], but of course: table1[game_name_en] : table2[game_name_en] is a n:1 relationship, since per session there can be multiple games played.

table2:

Tennis

Soccer

I want the first sessions (min(table1[login_date]) per customer where the customer only played 1 single game and won, hence, T001, where the customer played 2 different games must be ignored and the next session will be considered. There is one additional IF statement: If the customer won in the first session (for the game we chose in the slicer), the customer is discarded, the next game will not be considered.

The measure - or whichever way we go - has to dynamically react our selections in the table2[game_name_en] slicer.

If I select Tennis in the slicer, this is what I should get:

customer_number	login_session_number	login_date	game_name_en	duration
1	T002	28.07.21 08:46	Tennis	10
2	T007	28.07.21 20:58	Tennis	40

And if I choose Soccer, this would be the result:

customer_number	login_session_number	login_date	game_name_en	duration
2	T006	28.07.21 20:46	Soccer	5
3	T012	28.07.21 09:20	Soccer	5

Thank you for your time and looking very much forward to your ideas!

[v-xiaotang]

Hi @Anonymous 

try this 

create the measures

distCount = CALCULATE(DISTINCTCOUNT(Table1[game_name_en]),ALLEXCEPT(Table1,Table1[login_session_number]))

MeasureTest = 
var _selgamename=SELECTEDVALUE(Table2[game_name_en])
var _distCount=CALCULATE(DISTINCTCOUNT(Table1[game_name_en]),ALLEXCEPT(Table1,Table1[login_session_number]))
var _min=CALCULATE(MIN(Table1[login_date]),FILTER(ALL(Table1),Table1[customer_number]=MIN(Table1[customer_number])&&Table1[game_name_en]=_selgamename&&[distCount]=1))
return IF(MIN(Table1[login_date])=_min&&MIN(Table1[game_name_en])=_selgamename,1,0)

then put the second measure into the Filters on this visual

result:

Best Regards,

Community Support Team _Tang

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

[Anonymous]

@v-xiaotang I am sorry for updating this so late, but can we throw in the additional if sentence on the win/loss column, see my update. Thanks a whole lot!

[v-xiaotang]

@Anonymous 

if the solution helps, could you mark it as the solution? Thanks.

Best Regards,

Community Support Team _Tang

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

[v-xiaotang · Accepted solution]

@Anonymous 

🤣🤣🤣

OKay, then you need to add a column

distCountCol = CALCULATE(DISTINCTCOUNT(Table1[game_name_en]),ALLEXCEPT(Table1,Table1[login_session_number]))

then, create the measure

distCount2 = 
IF( CALCULATE(MAX(Table1[distCountCol]),ALLEXCEPT(Table1,Table1[customer_number]))<>1,3,1)

MeasureTest2 = 
var _selgamename=SELECTEDVALUE(Table2[game_name_en])
var _distCount=CALCULATE(DISTINCTCOUNT(Table1[game_name_en]),ALLEXCEPT(Table1,Table1[login_session_number]))
var _min=CALCULATE(MIN(Table1[login_date]),FILTER(ALL(Table1),Table1[customer_number]=MIN(Table1[customer_number])&&Table1[game_name_en]=_selgamename&&[distCount2]=1))
return IF(MIN(Table1[login_date])=_min&&MIN(Table1[game_name_en])=_selgamename,1,0)

then put the MeasureTest2 into Filters on this visual

result

Best Regards,

Community Support Team _Tang

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

[Anonymous]

Hi again, 

I cleaned the question up. Any ideas are super welcome, even if completely different approach.