Count parent record column strings per row

[123PS]

Hi All,

I am trying to show a count of the number of parent records per record. all parent records have the same "REC" string so I was going to count those but I am unable to get it to work right now. Any help would be greatly appreciated!

I tried this but it didnt work but i think i am close. I am getting 0 for all the results:

Results=

VAR _rows = { [parent 1], [parent 2], [parent 3], [parent 4], [parent 5], [parent 6], [parent 7], [parent 8], [parent 9]}

VAR _count = COUNTROWS (Filter (_rows, [Value] = "REC"))

Return IF (_count > 0, _count, 0)

I also tried this but also did not work. I got the error message: "A table of multiple values was supplied where a single value was expected"

Results 2 =

VAR _rows = { [parent 1], [parent 2], [parent 3], [parent 4], [parent 5], [parent 6], [parent 7], [parent 8], [parent 9]}

VAR _count = COUNTROWS(FILTER('Table - Name',CONTAINSSTRING(_rows, "REC")))

RETURN _count

Sample Data:

Record	Parent 1	Parent 2	Parent 3	Parent 4	Parent 5	Parent 6	Parent 7	Parent 8
REC00001	REC00009	REC000015	REC000021	REC000026	REC000030	REC000033	REC000035	REC000036
REC00002	REC000010	REC000016	REC000022	REC000027	REC000031	REC000034
REC00003	REC000011	REC000017	REC000023	REC000028	REC000032
REC00004	REC000012	REC000018	REC000024	REC000029
REC00005	REC000013	REC000019	REC000025
REC00006	REC000014	REC000020
REC00007
REC00008

Expected Outcome:

Record	Number of Parent Records
REC00001	8
REC00002	6
REC00003	5
REC00004	4
REC00005	3
REC00006	2
REC00007	0
REC00008	0

[v-cgao-msft · Accepted solution]

Hi @123PS ,

Please tirm and clean data first:

then pelase create a measure:

Number of Parent Records = 
VAR _p1 = COUNTA('Table'[Parent 1])
VAR _p2 = COUNTA('Table'[Parent 2])
VAR _p3 = COUNTA('Table'[Parent 3])
VAR _p4 = COUNTA('Table'[Parent 4])
VAR _p5 = COUNTA('Table'[Parent 5])
VAR _p6 = COUNTA('Table'[Parent 6])
VAR _p7 = COUNTA('Table'[Parent 7])
VAR _p8 = COUNTA('Table'[Parent 8])
VAR _result = _p1+_p2+_p3+_p4+_p5+_p6+_p7+_p8+0
RETURN
_result

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Ritaf1983]

Hi @123PS 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[123PS]

I corrected it and added the neccessary details. Hopefully that is sufficient.