Forum Discussion

sfernamer's avatar
sfernamer
Icon for Helper III rankHelper III
3 years ago
Solved

ExamplePbix Added - Select first row of different rows with same calculated column value

Hi!   I'm working with basketball data and trying to get how much time is playing every defensive five (column). With the help of the community, I found the correct DAX formula to calculate it (the...
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , sfernamer 

    Thanks for your quick response ! Do you mean the [TimeOnCount] vaule in the [Def3] context filter is wrong .

    You can try to use this dax to get the  [TimeOnCount] .

    TimeOnCount Test = var _t  =FILTER( SUMMARIZE(ALLSELECTED('Hoja1'),'Hoja1'[Full_Quarter] , 'Hoja1'[Time_Def] , 'Hoja1'[Defensive Five] , 'Hoja1'[Def3] ,'Hoja1'[TimeOnCourt] , "flag" , [Test_Flag 2]) , [flag] = 1)
var _cur_def3 =VALUES(Hoja1[Def3])
var _t2= SUMMARIZE(_t ,[Def3],"timeoncount" ,[Grouped_TimeOnCourt_D5])
return
SUMX(FILTER(_t2,[Def3] in _cur_def3) , [timeoncount])

    The result is as follows:

     

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi , sfernamer 

Thanks for your quick response and sorry for the delay response due to my two holidays.

For your need , i think i may got a missing to your need, and i am not fully understand the "For a Full_Game and Full_Quarter column, I check the rows with the same TimeOnCourt and the same Time_Def. In this combination, there should be always only one Defensive_Five value. "

In my logic , i just check the same [TimeOnCount] and not to check the [Time_Def].

 In my logic, i check the same   [TimeOnCount], then i just to find the lowest Index whose [TimeOnCount] are the same and just return 1 and -1 . 

And now i think the logic you need are not this , can you more explain your logic by some sample data?

Such as giving me some conditional sample data and the end result you want to get in the end?

And what the fields you need to put on the visual ?

 

I am not sure my understanding is the same as yours.

And for this sample data you provide , do you mean that we need to check:

(1)  the [TimeOnCount] and the [Time_Def] are all the same.    (YES)

(2)Then i do not know the next step to find the lowest index. In my thought , i will use the current [Full_Game] ,[Full_Quarter],[TimeOnCount],[Time_Def] to find the how many [Defensive Five] in this table?  (In your sample , there are two [Defensive Five] = "PLAYER2 ..." and "PLAYER3 ...")??

(3)Then we need to get the each lowest [Index] of each [Defensive Five] ?

"PLAYER2 ..."   ----- > 19242      "PLAYER3 ..."----->19257

(4)So in the end we keep the all the [Defensive Five]= "PLAYER2 ..." data ??

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

sfernamer's avatar
sfernamer
Icon for Helper III rankHelper III
3 years ago

Hi v-yueyunzh-msft 

 

Firstly, hope you could enjoy your holidays. Don't have to apologize, I truly appreciate all your effort to help me. I'm gonna try to reply about your understanding:

 

I am not sure my understanding is the same as yours.

And for this sample data you provide , do you mean that we need to check:

(1)  the [TimeOnCount] and the [Time_Def] are all the same.    (YES) That's correct, we need to check the rows with the same [Time_Def] and same [TimeOnCount].

(2)Then i do not know the next step to find the lowest index. In my thought , i will use the current [Full_Game] ,[Full_Quarter],[TimeOnCount],[Time_Def] to find the how many [Defensive Five] in this table? Yes. usually, the result of this check should be 1 (only one Defensive Five value) except for the specific casuistry found due to a mistake in the data entry.    (In your sample , there are two [Defensive Five] = "PLAYER2 ..." and "PLAYER3 ...")??  Yes. Because it's the specific casuistry where it was an error in a data entry. Here, the result of your check it's gonna be 2 instead of 1.

(3)Then we need to get the each lowest [Index] of each [Defensive Five] ? It could be a way to find the lowest Defensive Value for this subset (the one referred in step 1, same [Time_Def] and [TimeOnCourt]). In the end, the goal is to mark the "PLAYER2..." with 1 and the "PLAYER3..." as -1.

"PLAYER2 ..."   ----- > 19242      "PLAYER3 ..."----->19257

(4)So in the end we keep the all the [Defensive Five]= "PLAYER2 ..." data ?? That's correct. We should mark all these rows for the "PLAYER2..." as 1 and all the rows "PLAYER3..." with same [Time_Def] and [TimeOnCount]. Like the example below (already added).

 

And what the fields you need to put on the visual ? The fields can be "DefensiveFive" or "Def3" columns, depending on the interest but I guess that if we are able to mark correctly the rows with the Flag, this could not affect us.

 

I do think that your understanding is the correct one. I don't know if you could access but in the link (https://drive.google.com/drive/folders/1SlzwO6bgBBgrftwrVPGRVBE9KkIy_YqJ ) you can find an Excel file with all rows of the dataset and the Flag_Expected as a result of the measure and the dated pbix with the 3 measures you told me (20220120...).

 

Thank you so much for your time!

 

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi,  sfernamer 

    Thanks for your quick response and your patient description.
    In the last , i just judge the [TimeOnCount] field and ignore the [Time_Def] field. 
    I re-create the statement in dax, you can try to use this.

     

    Test_Flag 2 = var _tt =ALLSELECTED('Hoja1')
    var _t = SUMMARIZE(_tt ,'Hoja1'[Index],'Hoja1'[Full_Quarter],'Hoja1'[Time_Def] , 'Hoja1'[Defensive Five] , 'Hoja1'[TimeOnCourt])
    var _t2 =ADDCOLUMNS(_t, "same_flag" , var _timedef = [Time_Def] var _timroncount = [TimeOnCourt] return COUNTROWS( FILTER(_t , [Time_Def]=_timedef && [TimeOnCourt]= _timroncount)))
    var _t3 =ADDCOLUMNS( _t2 , "count_defensive" , var _same_flag = [same_flag] var _timedef = [Time_Def] var _timroncount = [TimeOnCourt] var _same_table = DISTINCT( SELECTCOLUMNS( FILTER(_t , [Time_Def]=_timedef && [TimeOnCourt]= _timroncount),"five",[Defensive Five])) return COUNTROWS(_same_table))
    var _t4 =ADDCOLUMNS(_t3, "min_five" , var _timedef = [Time_Def] var _timroncount = [TimeOnCourt] var _same_table =FILTER(_t , [Time_Def]=_timedef && [TimeOnCourt]= _timroncount) var _min_index =MINX(_same_table,[Index]) var _five_name =MAXX( FILTER(_same_table,[Index]=_min_index) , [Defensive Five])
    return IF([count_defensive]=1,[Defensive Five], _five_name))
    var _t5 =ADDCOLUMNS(_t4,"show_flag" , IF([same_flag]=1 ,1, IF([Defensive Five]=[min_five] ,1,0)))
    var _cur_index= MAX('Hoja1'[Index])
    var _flag = MAXX( FILTER(_t5 ,[Index]= _cur_index) , [show_flag])
    return
    IF(_flag=0,-1,1)

     

     

    The result is as follows. i hope it will be right now!

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

    • sfernamer's avatar
      sfernamer
      Icon for Helper III rankHelper III
      3 years ago

      Hi v-yueyunzh-msft 

       

      Thank you so much for your time. I checked your new measure and looks like it's working for all cases of the casuistry, so I'm gonna accept it as a solution. 

       

      Thanks again for your time.

    • sfernamer's avatar
      sfernamer
      Icon for Helper III rankHelper III
      3 years ago

      Hi v-yueyunzh-msft 

       

      I would like to ask you one last thing related to the visuals. The flag is working perfectly for DefensiveFive but, when I have to use the same for another columns like the Def3 I told you above, I have some problems working with it. 

       

      Let's see the example below, for a specific "DefensiveFive" value, I have a "Def3" value (it's the value of "DefensiveFive" but only concatenating the first, third and fifth position). Here, there are, for the same "Def3", 2 possible values of Defensive Five (it could be N values). Some of them are flagged as -1 with the Flag. How could I get the TimeOnCourt value only bearing in mind the ones marked in the flag as 1? 

       

      In the example below, the expected result for the visual in the right side above is 0:02:05 instead of the calculated 0:07:27.

       

      When grouping the Def3 value, it's not bearing in mind that the ones marked as -1 for the  DefensiveFive, should not be counted.

       

       

      Thank you for your time and patience, v-yueyunzh-msft 

       

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi , sfernamer 

        Thanks for your quick response ! Do you mean the [TimeOnCount] vaule in the [Def3] context filter is wrong .

        You can try to use this dax to get the  [TimeOnCount] .

        TimeOnCount Test = var _t  =FILTER( SUMMARIZE(ALLSELECTED('Hoja1'),'Hoja1'[Full_Quarter] , 'Hoja1'[Time_Def] , 'Hoja1'[Defensive Five] , 'Hoja1'[Def3] ,'Hoja1'[TimeOnCourt] , "flag" , [Test_Flag 2]) , [flag] = 1)
var _cur_def3 =VALUES(Hoja1[Def3])
var _t2= SUMMARIZE(_t ,[Def3],"timeoncount" ,[Grouped_TimeOnCourt_D5])
return
SUMX(FILTER(_t2,[Def3] in _cur_def3) , [timeoncount])

        The result is as follows:

         

         

        Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

         

        Best Regards,

        Aniya Zhang

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