Forum Discussion
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 shouldn't be counted.
So for organization A clause number 6.0 is present and therefore 6.1 and 6.2 shouldn't be counted. Clause 6.0 itself is always counted. The total number of clauses for this organization is 3.
| Organization | Clause | Count |
| A | 2.1 | Yes |
| A | 6.0 | Yes |
| A | 6.1 | No |
| A | 6.2 | No |
| A | 7.1 | Yes |
| B | 5.2 | Yes |
| B | 7.0 | Yes |
| B | 7.1 | No |
I would like to create the third column above, so I can just use a filter on each page. Of course it is also possible to create a general measure and place it in a table with Organization, but I would also like to visualize the total number of clauses (e.g. by country) with the above logic.
How could I create this calculated column?
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
2 Replies
- amitchandak
Super User
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") 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
