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

Anonymous's avatar
Anonymous
Not applicable
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