Count rows by subgroup depending on other rows

[Anonymous]

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?

[Anonymous · Accepted solution]

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]

@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")