Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ValeriaBreve
Post Patron
Post Patron

Help with virtual tables

Hi,

I am trying to build a virtual table and lookup a valuScreenshot 2022-03-07 211842.jpge 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:

Screenshot 2022-03-07 212318.jpg

Thanks a lot in advance for your help!

Kind regards

Valeria

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

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

vchenwuzmsft_0-1646905768393.png

result:

vchenwuzmsft_1-1646905870865.png

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.

View solution in original post

5 REPLIES 5
v-chenwuz-msft
Community Support
Community Support

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

vchenwuzmsft_0-1646905768393.png

result:

vchenwuzmsft_1-1646905870865.png

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.

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
Post Patron
Post Patron

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
Solution Sage
Solution Sage

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.

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...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors