How to count the measure

Question

Dear all,&nbsp;I have a table name "Sheet1" like this:SeriesNoF881234F881235F881236F881237F881240A253456A253457A253459A253460&nbsp;I've used the following query to figure out, which number is missing in the sequency:Missing_No =VAR cur_serrie =&nbsp; &nbsp; SELECTEDVALUE ( 'Sheet1'[Series] )VAR tmp =&nbsp; &nbsp; FILTER ( ALL ( 'Sheet1'), 'Sheet1'[Series]= cur_serrie )VAR tmp1 =&nbsp; &nbsp; CALCULATETABLE ( VALUES ( 'Sheet1'[No]), tmp )VAR max_no =&nbsp; &nbsp; MAXX ( tmp, [No] )VAR min_no =&nbsp; &nbsp; MINX ( tmp, [No])VAR tmp2 =&nbsp; &nbsp; GENERATESERIES ( COALESCE ( min_no, 0 ), COALESCE ( max_no, 0 ), 1 )VAR tmp3 =&nbsp; &nbsp; EXCEPT ( tmp2, tmp1 )RETURN&nbsp; &nbsp; CONCATENATEX ( tmp3, [Value], "," )It returns to what I want to see, when I show it with table:My question is, how can I add 1 more column for the counting of the missing number.It should be 1 for the first row, and 2 for the second row accroding to the missing number.&nbsp;Is there anyway we can count the total number missing as well, in this case is 3.&nbsp;Thank you for your help and support 🙂

ba_pete · Accepted Answer

Hi&nbsp;MightyRabbit&nbsp;,
&nbsp;
You could try an additional measure something like this to count at the row level:
&nbsp;
_noofMissing =
LEN([Missing_No]) - LEN(SUBSTITUTE([Missing_No], ",", "")) + 1
&nbsp;
&nbsp;
You could further try using the above measure within a SUMX to iterate over one of your tmp tables and sum up the total.
&nbsp;
Pete

Forum Discussion

How to count the measure

2 Replies

Related content

Count Measure

Counting nulls

Create count measure

Count Values multiplied by a measure

Overlap Count Measure

Series	No
F88	1234
F88	1235
F88	1236
F88	1237
F88	1240
A25	3456
A25	3457
A25	3459
A25	3460