Dax command to count occurances of strings across multiple columns/cell with comma deliminated list

[RichardTTH]

Hi all!

I'm working with a database that contains a list of projects/facilities and the products they produce. The products are listed both in a single cell separated by commas and I have split the cell into multiple columns containing a single product. I have also created a separate table containing a list of all the unique products. For example, here a rough idea of what the table looks like (though the full version has 6 product columns since that's the highest number of products). It also has a variety of other attributes for each facility that I would like to be able to filter the visual with i.e. Location etc.

Project	Product Sets	Product 1	Product 2	Product 3
Facility A	Tea, Coffee, Filters	Tea	Coffee	Filters
Facility B	Coffee, Filters	Coffee	Filters
Facility C	Coffee	Coffee

The separate table of unique products:

Unique Products

Tea

Coffee

Filters

Target end result:

Product	Count of Rows/Projects
Tea	1
Coffee	3
Filters	2

I can get accurate results for one column at a time by creating a relationship between the Unique Product and one of the Product 1/2/3 columns, but not for the product set or other numbered columns. 

I'm not sure if Dax is the appropriate mechanism for accomplishing this, but from the threads I've viewed it seems like the best way to go. Is there a way to go about calculating this with the database set up as it is? Are there any changes I need to make to the databse to calculate it?

Thanks in advance for any help!

[lbendlin · Accepted solution]

When data is coming in a format like that your first action is to unpivot it to make it usable.

Then when you load it to Power BI the rest can be done even with implicit measures, no code required.

[RichardTTH]

Perfect, this solved my issue. Thanks a bunch!

[johnt75]

I would change the model. Have a table for all your facilities, with location etc data in it. There are a couple of ways you could handle the projects and products. A lot would depend on how much, if any, info you have that is specific to a project. If there is a lot of data on projects then have a separate table for projects which you would link to the facilities table. You would also then want a table containing Project and Product, one row for every combination, and link this table to your Projects and Unique Products tables.

If there isn't much data about projects then combine that with the products and have a table containing Facility, Project, Product, with 1 row for every combination of project and product.

You would still want the separate table for unique products, which you would link to the combined table.

The version where you combine Facility, Project and Product into 1 table would make for simpler DAX. The number of projects would be a simple DISTINCTCOUNT( 'Table'[Project] ) and this would be filterable by product as well as anything from the facilities table.