Newbie needing simple? table calculation help

[Anonymous]

Hi - I'm hoping someone can help me.   I'm very new, literally hours in, and a recent convert to Power BI from Tableau.  I just couldn't take the special way you needed to do and think about things differently using Tableau.  Unfortunately, I did have some dashboards already built that I need to re-create in Power BI.  Anyway...

I need to count the occurrence of a name in a semicolon separated column of data.  I need the count to be in a table with the count of another name field.  I tried some simple functions and can't seem to get the calculation to work.  In the example data attached, I need a count of the occurrences of the Primary & Back-up name columns.  A simple count only counts the occurrences where the entire combined data exists.  Hard to explain but looking at the data should help.  I also don't know how to do simple stuff yet like add grand totals and subtotals to the columns.  I'm good at Excel pivot tables, and I'm guessing it is similar in a Matrix - - just haven't figured out where everything is yet.

I have a spreadsheet with example data in a table, and the answer I'd like to obtain from in a Matrix.  I also have the data in Power BI Matrix desktop file.  I'd appreciate if someone could help me out sooner than later if possible.  The link will take you to the files.  https://drive.google.com/open?id=1dYXi75tZ4TXxiBuMjfLmkPTP8c7WRNbI

[Greg_Deckler]

I created a quick measure for things like this, you can see it here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Count-of-List-Items/m-p/350084

Welcome!!

[Anonymous]

Hi - thanks for the reply, but I don't think this is the solution I was looking for.  For your table, the answer I'd be looking for would be the count of all the occurrences of "One" in the column, the count of all the occurences of "Two" in the column, and the count of all the occurences of "Three" in the column.  Example;

Column 1      Column 2                     Count of Occurences of Column 1 in Column 2

One               One                                  3

Two               One, Two                          2

Three             One, Two, Three               1

If you do understand, I have a sligthly more complex example in the files.  If you could put the solution in the Power BI file or use the following data set.  I REALLY appreciate the help.

  Type              Primary                Back-up

Supplier	Robert Smith	James Johnson;Maria Garcia
Customer	Maria Garcia	Mary Smith;Bob Williams;James Johnson
Internal	Mary Smith	Robert Smith
Internal	James Johnson	Bob Williams;Robert Smith;Mary Smith
Customer	Bob Williams	Robert Smith;Maria Garcia
Supplier	Robert Smith	James Johnson
Supplier	Robert Smith
Supplier	Robert Smith
Internal	Mary Smith	Maria Garcia
Customer	Maria Garcia	Bob Williams;James Johnson
Internal	Bob Williams	James Johnson;Mary Smith
Customer	Robert Smith	Bob Williams

The answer I'm looking for is

	Customer			Internal			Supplier
Primary	Count of Primary	Count of Back-up	Total Customer	Count of Primary	Count of Back-up	Total Internal	Count of Primary	Count of Back-up	Total Supplier	Total Count of Primary	Total Count of Back-up	Grand Total
Bob Williams	1	3	4	1	1	2	0	0	0	2	4	6
James Johnson	0	2	2	1	1	2	0	2	2	1	5	6
Maria Garcia	2	1	3	0	1	1	0	1	1	2	3	5
Mary Smith	0	1	1	2	2	4	0	0	0	2	3	5
Robert Smith	1	1	2	0	2	2	4	0	4	5	3	8
Total	4	8	12	4	7	11	4	3	7	12	18	30

[Ashish_Mathur]

Hi,

You may refer to my soluion in this file.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

Go to Home > Edit Queries and click on the Table named Data to see the transformation steps i applied.  Here are the answers to your specific questions:

1. Using Data > Split column > Advanced > By rows
2. Done in the Query Editor.  Once click solution to create an Index column
3. Done in the Query Editor.

If my previous reply helped, please mark as Answer.

[Anonymous]

Ashish - thanks for the quick response and sorry for my delay getting back to you.  You did it!  I wanted to verfiy that I could re-create the solution on my side but got caught up in some other work. 

[Ashish_Mathur]

You are welcome.

[Anonymous]

Looks like it works Ashish, thanks!  I have some questions so I can repeat the solution.

- How did you split the backup out?

- How do I create the column "Index"?  Is is a basic function that Power BI will do in creating the split?

- How do I create the column "Back-up combination", it looks like a concatination of the index and the split backup column but it is not a calculated measure.  How do you do this?

I'm sorry for the newbie questions.  Thanks for your valuable time.

[Anonymous]

Also - is the table pirmary supplier names needed?  It is blank as far as I can see.