cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rimgri Member
Member

Calculate table to bring rows from another table

Hi,

Could anyone help me by answering what DAX expression should I use if I want to make a calculated table, which brings just those rows that have another table:

 

Table 1

Prod Order No_Prod Order Line_ProductVersionConversionProd QTY
GU0000020GU0000020_20000ZZZZ-0001ZZZZ-000110
GU0000020GU0000020_10000FFFF-0001FFFF-000110
GU0000046GU0000046_10000ZZZZ-0002ZZZZ-000211196
GU0000282GU0000282_10000LLLL-0001LLLL-00012802310
GU0000283GU0000283_10000LLLL-0001LLLL-00012800

 

Table 2

VersionItem No_BOM quantity
FFFF-0001ME-781,02
FFFF-0001MEK-78131,02
FFFF-0001MK-1591,02
FFFF-0001MI-0011,02
FFFF-0001MPP-0120,165
FFFF-0001MG-00000,1256
FFFF-0001MZ-500,1275
FFFF-0001MK-061,005
FFFF-0001RD-10390,0013
FFFF-0001HR-00050,132
FFFF-0001HP-00130,0009
FFFF-0001ZZZZ-00020,1963
FFFF-0001ZZZZ-00010,054
FFFF-0001FAAS-0001-0,0236
FFFF-0001HP-0010,002
ZZZZ-0001HKA2-0020,02
ZZZZ-0001HV09-0111,82
ZZZZ-0001III-00010,216
ZZZZ-0001WWW-0041,2636
ZZZZ-0001H10230,048
ZZZZ-0002ZFOS-01281
ZZZZ-0002HKA2-0020,02
ZZZZ-0002HS03-0100,0044
ZZZZ-0002III-00010,1
ZZZZ-0002WWW-0040,7906
ZZZZ-0002H10230,085
LLLL-0001III-000130,16
LLLL-0001ICC-00037,8
LLLL-0001IMM-000212,61
LLLL-0001IMM-000512,74
LLLL-0001IVV-00060,78
LLLL-0001IRR-000320,54
LLLL-0001ISS-00011,43
LLLL-0001WWW-004173,94

 

The result shoul be

Table 1Table 1Table 2Table 2
Prod Order No_ProductVersionItem No_
GU0000020ZZZZ-0001ZZZZ-0001HKA2-002
GU0000020ZZZZ-0001ZZZZ-0001HV09-011
GU0000020ZZZZ-0001ZZZZ-0001III-0001
GU0000020ZZZZ-0001ZZZZ-0001WWW-004
GU0000020ZZZZ-0001ZZZZ-0001H1023
GU0000020FFFF-0001FFFF-0001ME-78
GU0000020FFFF-0001FFFF-0001MEK-7813
GU0000020FFFF-0001FFFF-0001MK-159
GU0000020FFFF-0001FFFF-0001MI-001
GU0000020FFFF-0001FFFF-0001MPP-012
GU0000020FFFF-0001FFFF-0001MG-0000
GU0000020FFFF-0001FFFF-0001MZ-50
GU0000020FFFF-0001FFFF-0001MK-06
GU0000020FFFF-0001FFFF-0001RD-1039
GU0000020FFFF-0001FFFF-0001HR-0005
GU0000020FFFF-0001FFFF-0001HP-0013
GU0000020FFFF-0001FFFF-0001ZZZZ-0002
GU0000020FFFF-0001FFFF-0001ZZZZ-0001
GU0000020FFFF-0001FFFF-0001FAAS-0001
GU0000020FFFF-0001FFFF-0001HP-001
..
1 ACCEPTED SOLUTION

Accepted Solutions
v-danhe-msft Super Contributor
Super Contributor

Re: Calculate table to bring rows from another table

Hi @rimgri ,

Based on my test, you could refer to below formula:

Result = 
GENERATEALL (
    SELECTCOLUMNS(Table1,"Product",[Product],"Prod Order No_",[Prod Order No_]),
    VAR Table1ID = [Product]
    RETURN
        SELECTCOLUMNS (
            CALCULATETABLE ( Table2, Table2[Version] = Table1ID ),
            "Item No_", Table2[Item No_],"Version",Table2[Version])
        )
Result:
1.PNG
You could also download the pbix file to have a view.
 
Regards,
Daniel He
Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User
Super User

Re: Calculate table to bring rows from another table

@rimgri  Please try using "Merge Queries" in Power Query Editor. 

 

let
    Source = Table.NestedJoin(Test205Join2,{"Version"},Test205Join1,{"Version"},"Test205Join1",JoinKind.RightOuter),
    #"Expanded Test205Join1" = Table.ExpandTableColumn(Source, "Test205Join1", {"ProdOrderNo", "Product"}, {"Test205Join1.ProdOrderNo", "Test205Join1.Product"})
in
    #"Expanded Test205Join1"

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





rimgri Member
Member

Re: Calculate table to bring rows from another table

Thanks, @PattemManohar but how can I get the same result in DAX?

I'm not very familiar with M query and I will need more calculations and joins with this table.

Super User
Super User

Re: Calculate table to bring rows from another table

@rimgri  You no need to write M-code for this. That was the auto generated code after performing the steps in Power Query.

 

Here is the screenshot of the wizard in Power Query for Merge Queries.

 

image.pngimage.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Super User
Super User

Re: Calculate table to bring rows from another table

@rimgri - Are you looking for something like this?

 

Table 2 = 
VAR __table1 = SELECTCOLUMNS('Table14',"ProdOrderNo",[Prod Order No_],"Product",[Product])
VAR __table2 = SELECTCOLUMNS('Table15',"Version",[Version],"ItemNo",[Item No_])
VAR __final = GENERATE(__table1,__table2)
RETURN
__final

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

rimgri Member
Member

Re: Calculate table to bring rows from another table

Thanks, @Greg_Deckler 

 

But it bring all versions. For GU*20 it's just two of them. How can I describe in DAX that he would join those data that maches other table value.

Screenshot_14.pngScreenshot_15.png

 

The pbix if you woud like to try:

https://www.dropbox.com/s/xfv1ei76lr92c4q/Community2.pbix?dl=0

 

v-danhe-msft Super Contributor
Super Contributor

Re: Calculate table to bring rows from another table

Hi @rimgri ,

Based on my test, you could refer to below formula:

Result = 
GENERATEALL (
    SELECTCOLUMNS(Table1,"Product",[Product],"Prod Order No_",[Prod Order No_]),
    VAR Table1ID = [Product]
    RETURN
        SELECTCOLUMNS (
            CALCULATETABLE ( Table2, Table2[Version] = Table1ID ),
            "Item No_", Table2[Item No_],"Version",Table2[Version])
        )
Result:
1.PNG
You could also download the pbix file to have a view.
 
Regards,
Daniel He
Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 2,961 guests
Please welcome our newest community members: