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
Community SupportHi , 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
Helper III
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.
- v-yueyunzh-msft
Hi , You can try to use this dax , i add some judgement in it:
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 _time = [TimeOnCourt] var _min_index =MINX( FILTER( _tt ,[TimeOnCourt] = _time ),[Index]) return IF([flag]>1,_min_index)) var _t4 =ADDCOLUMNS(_t3 , "test2" , var _test = [test] var _test_table= FILTER(_tt,[Index] = _test) var _full=MAXX(_test_table,[Full_Quarter]) var _time_def = MAXX(_test_table,[Time_Def]) var _defensive =MAXX(_test_table,[Defensive Five]) return IF([flag]=1,1,IF([Full_Quarter]=_full && [Time_Def]=_time_def && [Defensive Five]=_defensive,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)If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
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, I'd like to thank your time. After checking your last measure, the flag is still not working for all cases so, following your advice, I decided to add you 2 files in the Google Drive link (https://drive.google.com/drive/folders/1SlzwO6bgBBgrftwrVPGRVBE9KkIy_YqJ ).
"20220120_ExamplePbix_v4" - Pbix with all rows of the dataset and the measures you explained to me in earlier replies.
"DatawithFlagExpected" - Excel file with the column "Flag_Expected", where, after checking all rows, I put if it's 1 or -1.
The logic is the explained earlier: 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. When this is not true (due to the data entry mistake), I choose the Defensive_Five value with the MIN (Index column).
Hope this can be useful for the analysis.
Sergi