Forum Discussion

123PS's avatar
123PS
Regular Visitor
2 years ago
Solved

Count parent record column strings per row

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:

RecordParent 1Parent 2Parent 3Parent 4Parent 5Parent 6Parent 7Parent 8
REC00001REC00009REC000015REC000021REC000026REC000030REC000033REC000035REC000036
REC00002REC000010REC000016REC000022REC000027REC000031REC000034  
REC00003REC000011REC000017REC000023REC000028REC000032   
REC00004REC000012REC000018REC000024REC000029    
REC00005REC000013REC000019REC000025     
REC00006REC000014REC000020      
REC00007        
REC00008        

 

Expected Outcome:

RecordNumber of Parent Records
REC000018
REC000026
REC000035
REC000044
REC000053
REC000062
REC000070
REC000080

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

3 Replies