Forum Discussion
Extract/Create New Table
Morning, All,
I have a sinlge table in my model called "Supplier Interests." One of the column names is "Interest Type." In this intrest type column, there are about 60 different values. Some are regions, some are lines of business, etc.
The first column in my table is Supplier ID. There are 98k+ total rows and 11k distinct values for supplier ID.
I want to create a new table that can be filtered in the visualization with filter context for "interest type." But I need to extract all of the regions and all of the interest types from this table. The goal is to create a filter that can identify Supplier ID -> Region-> Line of Business. But region and line of business are in the same column. I have been trying to do it with DAX, but I am not sure if it is possible or if we have to go to the backend (SQL Server, not PQE).
Any insight is appreciated.
Thank you,
Cam
7 Replies
Maybe this makes more sense: the three distinct entries in "interest type" should all be their own individual tables in the model with Supplier ID as the PK
In the Interest Type Column there are 3 distinct data points. Indusrty, Project, and Region. I want to separate all 3 of those into their own tables with Supplier ID being the Primary Key. Then add all three of those tables, filter context created, so be able to visualize: Supplier ID->Region-> and then the line of business as the final part.
I have tried creating a hierarchy, adding new tables, (may have the wrong DAX), and other filter context creating DAX.
I have tried Calculatetable and group by. I really just want to create three new tables from the three unique data point in a single column, such that they have filter context.
Roche’s Maxim of Data Transformation states: Data should be transformed as far upstream as possible, and as far downstream as necessary. It would be super helpful by adding a work-in-progress Power BI Desktop File (anonymized of course).
I understand the upstream principle, but that requires testing and approval, which is why I am trying to do it in PBI. And even though sharing anonymized WIP is an option, I am uncomfortable doing so. This is of course, for work.
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). This is standard when seeking assistance of Microsoft Communities, and others such as EnterpriseDNA Support Forums.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.
