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 

According to your description, you want to delete the row when the TimeOnCourt is the same and you need to keep the minest index row.

And thanks for the sample dat you provided.

We can click "New Table" and enter this:

Table 2 = ADDCOLUMNS( SUMMARIZE('Table','Table'[TimeOnCourt],'Table'[Time_Def],'Table'[Full_Quarter]) , "Defensive Five" , 
var _TimeOnCourt = [TimeOnCourt]  var _min_index = MINX( FILTER('Table','Table'[TimeOnCourt]=_TimeOnCourt) , [Index])
var _five =MAXX( FILTER('Table','Table'[Index] = _min_index) , [Defensive Five]) return _five)

Then we can meet your need , 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

 

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

    Hi v-yueyunzh-msft 

     

    Firstly, thank you for your reply. I tested and could work but I should model tables because there are more rows that can be used for TimeOnCourt visualizations, so I would need to find the way to do the same with the formula created but not creating a new table. Add 2 examples of the full table and how the "TimeOnCourt" was created (not in Power Query, it was created in visualization panel).

     

    Later, I would all an example of the full table for one game.