Help with virtual tables

[ValeriaBreve]

Hi,

I am trying to build a virtual table and lookup a value from a column of it. I have 2 tables that have a relationshio through the "Concx" field. I want to be able to add columns in the second table with the related PO numbers from the first table, in the form of "PO1 Rank1", "PO1 Rank2", etc

I can't get to a decent way of doing this.

As an example for Rank1 I built a virtual table:

VAR FilterNthProduct = FILTER ( Table1,Table1[Rank] = 1 )

However I don't know from here how to retrieve the PO2-related PO1 number. 

This is what I would like to obtain:

Thanks a lot in advance for your help!

Kind regards

Valeria

[v-chenwuz-msft · Accepted solution]

Hi @ValeriaBreve ,

I would prefer that you use power query to achieve this result.

Copy the po1 table and pivot the Rank field of this table,like this screenshot

result:

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ValeriaBreve]

Hello, thanks a lot! This is a really neat way of doing it. However, unfortunately in my case it is not so efficient as I am doing all transformations in DAX, so these tables do not exist in PowerQuery. But I will definitely remember this for the time where I have everything in the query editor 🙂

[ValeriaBreve]

Hi, thanks, however the idea is not to create another table.

I need to filter the existing Table1 for the rank I need and add a column to the existing Table2 per rank. This is why I would like to use a virtual table, not to multiply Table 2 filtered for each rank.However, once I filter Table2 creating a virtual table, I need to lookup the PO and to do that I need column names which I don't know how to get from  virtual table....

[Whitewater100]

HI:

For a new table you can try = SUMMARIZE('Table 2',
                                               'Table 2'[PO2], 'Table 2'[conc2], 'Table1'[PO2],  etc  ) just add table name and column for other columns. SUMMARIZECOLUMNS is good as well and you don't have to list a table name to begin like in SUMMARIZE. Just Table[Columns].

 This could be a start.

[ValeriaBreve]

OK I found the way. See Solved: Filtering a lookup column - Microsoft Power BI Community

You can actually filter directly in the Lookup value function which I was not aware about, no need to for a virtual table inbetween...