Forum Discussion
Helper IIIExamplePbix Added - Select first row of different rows with same calculated column value
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
Helper IIITo 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.
Community SupportHi , 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
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
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
- sfernamer
Thank you for your time. Maybe the change in a column name is due to anonymization made due to the confidential data. I apologize it happened. I tested your new formula in the same pbix and still mark some test_flags as -1 whne there is only a DefensiveFive value for a TimeOnCourt value. I added your measure to the pbix and renamed it. Furthermore, I show you some examples about what I wrote above. Hope it can be useful for the explanation because I do believe the measure is quite close to the goal.
Thank you so much for your time and your tries. I'm totally aware that it's a non common question and you are doing your best to help me, so thank you so much.
Link with the pbix: https://drive.google.com/drive/folders/1SlzwO6bgBBgrftwrVPGRVBE9KkIy_YqJ?usp=share_link
2 Examples are working perfectly:
The Test_Flag2 is working as expected, detecting the same value for TimeOnCourt and different DefensiveFive values and choosing the right one (the one with MIN index, as Test_Flag2 = 1).
Example 2:
For the same TimeOnCourt, there is ony one DefensiveFive value but it's being marked as -1. The result of the measure should be 1, compared to the above example and the problem.
Example 3:
The Test_Flag2 is marking the row as -1 when there is only one DefensiveFive for a TimeOnCourt value. The result of the measure should be 1 for this case.