Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count rows by subgroup depending on other rows

I would like to count rows (number of clauses) with following logic: - if within one organization a clause number ending on a zero is present the other clauses with the same starting number shou...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    You can create a calculated column as below:

    Count = 
VAR _curclause='Clause'[Clause]
VAR _mvalue=CALCULATE(MIN('Clause'[Clause]),FILTER(ALL('Clause'),'Clause'[Organization]=EARLIER('Clause'[Organization])
&&LEFT('Clause'[Clause], IF(SEARCH(".",'Clause'[Clause],1,0)=0,LEN('Clause'[Clause]),SEARCH(".",'Clause'[Clause],1,0)-1))=LEFT(_curclause, IF(SEARCH(".",_curclause,1,0)=0,LEN(_curclause),SEARCH(".",_curclause,1,0)-1))))
RETURN IF(_curclause=_mvalue,1,0)

    Best Regards

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , Try a new column like

 


new column =
var _min = minx(filter(Table, [Organization] =earlier([Organization]) , rounddown([Clause],0) = rounddown(earlier([Clause]),0)),[Clause])
return
if([Clause] =_min, "Yes", "No")