How to a expand one row to multiple rows ?

[ujwalynit]

Hi ,

Need help with Dax command . I have two tables - Sales and Recipes 

e.g.

Sales

SALES_DATE | POS_ITEM_ID |  POS_ITEM_NAME | SALES_UNITS

8/9/2019 | 13123213| Negroni | 10

7/9/2019 | 13123213| Negroni | 5

Recipe

POS_ITEM_ID | INGREDIENT_ID | INGREDIENT_NAME | INGREDIENT_UNITS

13123213 | 9494949 | Gin | 1

13123213 | 4848484 | Campari | 1

13123213 | 75757575| Vermouth | 1

How do i create a table ?

8/9/2019 | 13123213| Negroni | 10 |9494949 | Gin | 1

8/9/2019 | 13123213| Negroni | 10 |4848484 | Campari | 1

8/9/2019 | 13123213| Negroni | 10 |75757575| Vermouth | 1

7/9/2019 | 13123213| Negroni | 5|9494949 | Gin | 1

7/9/2019 | 13123213| Negroni | 5|4848484| Campari | 1

7/9/2019 | 13123213| Negroni | 5|75757575| Vermouth | 1

[SeanMcLarty · Accepted solution]

Use GENERATEALL to join the tables by POS_ITEM_ID

Sales and Recipies = GENERATEALL(
    Sales,
    SUMMARIZE(
        FILTER(
            Recipe,
            Recipe[POS_ITEM_ID] = Sales[POS_ITEM_ID]
        ),
        Recipe[INGREDIENT_ID],
        Recipe[INGREDIENT_NAME],
        Recipe[INGREDIENT_UNITS]
    )
)

[Zubair_Muhammad]

HI @ujwalynit 

You can use CROSSJOIN function in DAX to create a new calculated table from the Modelling tab

https://docs.microsoft.com/en-us/dax/crossjoin-function-dax