Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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.
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.
Perfect, this solved my issue. Thanks a bunch!
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |