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

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

To try to help people who could help me, I add a pbix file example to test with one game that shows the specific problem (due to bad data entry, not capable to correct right now).

 

Link pbix: https://drive.google.com/drive/folders/1SlzwO6bgBBgrftwrVPGRVBE9KkIy_YqJ?usp=share_link 

 

As you can see below, if there is a "TimeOnCourt" value and more than one value in other column, it shows both values. Here, the solution should be to add code to the "TimeOnCourt" calculated column to take the MIN(column Index) for these cases, but I tested to put it in different places and didn't work. 

 

For this game, the result in second table should be 0:40:00 instead of 0:43:03. The difference, in the example, is due to the specific mistake in data entry for Full Quarter = 2Q and Time_Def= 0:04:00, but the mistake could be in a different Quarter or Time_Def in other games so the solution has to be general.

 

Could you give me advice, please? Thx in advance.

 

 

  • 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 the sample .pbix file you provieded.

    You can create a measure like this:

    Measure = var _tt = ALLSELECTED(Hoja1)
var _t =  SUMMARIZE(_tt , 'Hoja1'[Def3] , "TimeOnCourt" , [Grouped_TimeOnCourt_D5])
var _t2 =  ADDCOLUMNS( _t , "flag" , var _time = [TimeOnCourt] var _count = COUNTROWS(  FILTER(_t , [TimeOnCourt] = _time)) return _count)
var _t3 =  ADDCOLUMNS(_t2 , "test" , var _def3 = [Def3] var _min_index =MINX(  FILTER( _tt , [Def3]=_def3 ),[Index])  return IF([flag]>1,_min_index)) 
var _t4 = ADDCOLUMNS(_t3 , "test2" , var _timecount = [TimeOnCourt] var _test = MINX( FILTER(_t3 , [flag]>1 && [TimeOnCourt] =_timecount),[test]) var _min_def3 =MINX(FILTER(_tt,[Index] = _test),[Def3])  
return IF(OR([Def3]=_min_def3,[flag]=1) ,1 ,-1)   )
var _cur_def3= MAX('Hoja1'[Def3])
var _test_flag =MAXX( FILTER(_t4, [Def3] = _cur_def3),[test2])
return
IF(_test_flag=-1,-1,1)

    Then we can put the measure on the "Filter on this visual " and configure it and then we can meet your need:

    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 all your support and help, it's being really useful to manage this data entry mistake.

       

      I tested your measure in the subset and worked but, when I added to the full dataset, it's marking as a Flag_Test = -1 some cases that don't have the same TimeOnCourt. I added you the new cases (basketball games) with the one you solved and 2 new tabs in the pbix to check all rows (filtered by Defender = MyTeam). 

       

      The new file is "ExamplePbix_v2.pbix" in this link (I can't add the file directly yet): https://drive.google.com/drive/folders/1SlzwO6bgBBgrftwrVPGRVBE9KkIy_YqJ?usp=share_link 

       

      Could you, please, give me advice? Thank you.

      • 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 i check the sample .pbix file , you put the differnt fields so the measure can not work , if you want the first page visual work , you need to create another measure like this :

        Test_Flag 2 = var _tt =ALLSELECTED('Hoja1')
var _t =  SUMMARIZE(_tt ,'Hoja1'[Full_Quarter],'Hoja1'[Time_Def] , 'Hoja1'[Defensive Five] , 'Hoja1'[TimeOnCourt])
var _t2 =  ADDCOLUMNS( _t , "flag" , var _time = [TimeOnCourt] var _count = COUNTROWS(  FILTER(_t , [TimeOnCourt] = _time)) return _count)
var _t3 =  ADDCOLUMNS(_t2 , "test" , var _def3 =[Defensive Five] var _min_index =MINX(  FILTER( _tt , [Defensive Five]=_def3 ),[Index])  return IF([flag]>1,_min_index)) 
var _t4 = ADDCOLUMNS(_t3 , "test2" , var _timecount = [TimeOnCourt] var _test = MINX( FILTER(_t3 , [flag]>1 && [TimeOnCourt] =_timecount),[test]) var _min_def3 =MINX(FILTER(_tt,[Index] = _test),[Defensive Five])  
return IF(OR([Defensive Five]=_min_def3,[flag]=1) ,1 ,-1)   )
var _cur_def3= MAX('Hoja1'[Defensive Five])
var _test_flag =MAXX( FILTER(_t4, [Defensive Five] = _cur_def3),[test2])
return
IF(_test_flag=-1,-1,1)

        Then we can get the right result :

        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