Measure to get count of items in table by ID

[TomSinAA]

I have this measure: 

Stops Per Route = var _id =  Stops[SYS_STATIC_ROUTE_ID]

var _t = SELECTCOLUMNS(FILTER(Stops,Stops[SYS_STATIC_ROUTE_ID]=_id) , "Site" , [SITE_NAME])

return

COUNTROWS(_t)

It is displaying the warning: A single value for column '' in table '' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 The Stops table has data like this:

SYS_STATIC_ROUTE_ID	SITE NAME
1	Site Name1
1	Site Name2
1	Site Name3
1	Site Name4
1	Site Name5
2	Site Name 1
2	Site Name 2
3	Site Name 8
3	Site Name 9
3	Site Name 10

[v-venuppu]

Hi @TomSinAA ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @TomSinAA ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @TomSinAA ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[maruthisp · Accepted solution]

Hi TomSinAA,

As per my uunderstanding, You want to count how many unique rows (or combinations) exist per SYS_STATIC_ROUTE_ID, grouped by route.
StopsPerRoute =
CALCULATE(
DISTINCTCOUNT(Stops[SITE_NAME]),
ALLEXCEPT(Stops, Stops[SYS_STATIC_ROUTE_ID])
)

CALCULATE checks to group by SYS_STATIC_ROUTE_ID

DISTINCTCOUNT checks duplicates are not double-counted

ALLEXCEPT keeps only the SYS_STATIC_ROUTE_ID grouping

Please let me know if this help. Thanks in advance.

Best Regards,  

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X 

[Ashish_Mathur]

Hi,

Just create a matrix visual and drag this measure there

Measure = countrows(Data)

[danextian]

Hi @TomSinAA 

You're writing a measure so the variable needs to return a scalar value. Wrap the column with MAX, MIN or SELECTEDVALUE.