How to count with many records

[Anonymous]

Hello experts,

I am stuck at something and i urgently need help.

I have a table from where i need to count Book id based on author name and then make graphs based on ranks for authors , Simple version of table is below. I have quite a few tables in relationship model, so adding another is not an option.

Author                  Book id

Ken                             123

Jerry                          545

Ken, Jerry               321

Sam ,Jerry,Ken         525

----Like this

I tried to use LOOKUP for "Ken" and get an extra 'Ken' cloumn with 1 when row has "Ken" and then used page filter for showing each ranked author in each page. but it is making the graph static as i needed to add this extra'Ken' column while graphing and also i have 200+ distinct authors.

I need to  add a measure to count book id based on authors name in row.

 How do i do this? Please help.

Thanks a lot, and this community is very helpful, everyday i am learning from experts, thanks a lot...

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, right click on the first column's heading and select Split > By delimiter > Comma > Advanced > By rows.  to your visual, drag Author from the Table and write this measure

=DISTINCTCOUNT(Data[Bood id])

Hope this helps.

[Anonymous]

Thanks, 

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.