topic Re: How to a expand one row to multiple rows ? in DAX Commands and Tips

How to a expand one row to multiple rows ?

ujwalynit — Tue, 10 Sep 2019 03:20:25 GMT

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

Re: How to a expand one row to multiple rows ?

Zubair_Muhammad — Tue, 10 Sep 2019 04:12:10 GMT

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

Re: How to a expand one row to multiple rows ?

SeanMcLarty — Tue, 10 Sep 2019 04:21:27 GMT

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]
    )
)