Search code examples
powerbidata-sciencepowerquerypowerbi-datasourcepowerbi-custom-visuals

How to add a new column with custom values, based on a WHERE clause from another table in PowerBi?

I am stuck while dynamically forming a new column based certain WHERE clause from another Table in PowerBi. To give more details, let's say I have a table with item numbers associated with a Customer Name. In another table, I have to add a new column, which will dynamically add the item numbers associated with a particular customer and append as a query parameter to a base url.

So, my first table looks like this:

enter image description here

The second table that I want is this:

enter image description here

The query parameter value in the URL, has to be dynamically based on a SELECT query with a WHERE clause and pick up the ItemNumbers using the Customer field which is common between both. So, how can this be done in PowerBi? Any help would be really appreciated :)

Solution

  • I have one table in my model "TableRol" if I want to summarize my Date as the string I can use CONCATENATEX;

    enter image description here

    URL = CONCATENATE(CONCATENATE("http:\\mysite.com\parametersHere\getitem?='",CONCATENATEX(VALUES('TableRol'[Date]), 'TableRol'[Date],";")),"'")