Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Aggregation/Filtering obstacles and how to overcome it

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

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    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.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi again, 

     

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

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi v-xiaotang 

       

      Thank you for your reply and here is some data

      table1:

      customer_number login_session_number login_dategame_name_enduration win/loss
      1T00128.07.21 07:29Tennis20w
      1T00128.07.21 07:29Soccer20w
      1T00228.07.21 08:46Tennis10w
      1T00328.07.21 08:53Tennis5l
      2T00628.07.21 20:46Soccer5w
      2T00728.07.21 20:58Tennis40w
      2T00828.07.21 21:01Soccer5l
      3T01128.07.21 09:07Tennis25l
      3T01228.07.21 09:20Soccer5w
      3T01328.07.21 09:23Tennis25w

       

      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_numberlogin_session_numberlogin_dategame_name_enduration
      1T00228.07.21 08:46Tennis10
      2T00728.07.21 20:58Tennis40
           

       

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

      customer_numberlogin_session_numberlogin_dategame_name_enduration
      2T00628.07.21 20:46Soccer5
      3T01228.07.21 09:20Soccer5


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

      • v-xiaotang's avatar
        v-xiaotang
        Icon for Community Support rankCommunity Support
        5 years ago

        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.