Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Innerjoin Challenge

  Hello Power friends, some help, please.   I have two tables as below:   Planned Sales id_store, id_department, date, sales on month (planned)   01, 222,01/01/2018, 100 01, 222,01/02/2018, ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

    Try this formula, please. Please also download the demo from the attachment.

    Table =
SELECTCOLUMNS (
    ADDCOLUMNS (
        'plan',
        "New Col",
        VAR salesValue =
            LOOKUPVALUE (
                sales[Sales],
                sales[id_store], [id_store],
                sales[id_department], [id_department],
                sales[date], [date]
            )
        RETURN
            IF ( ISBLANK ( salesValue ), [Planed], salesValue )
    ),
    "id_store", [id_store],
    "id_department", [id_department],
    "date", [date],
    "Sales Planed or Real", [New Col]
)

    Innerjoin-Challenge

     

    Best Regards,
    Dale

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
7 years ago

Hi Anonymous

 

So do you want your new table to have 1 row per day/store/department?

 

And if there is data for it in the Sales table, then use that otherwise, use data from the Planned Sales table?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Phil_Seamark,

 

The merged final table could be like that:

 

id_store, id_department, date (DD/MM/YYYY), sales on month (real or planned) ou both (two diferrents colluns)

01, 222, 01/01/2018, 100

01,111, 01/01/2018, 120

01, 111, 02/02/2018, 212

02, 333, 01/01/2018, 200

02, 444, 02/01/2018, 120

(...)

 

The date of planed sales table begins in 01/01/2018 and ends in 31/12/2018.

Thanks again for you time.

 

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    Try this formula, please. Please also download the demo from the attachment.

    Table =
SELECTCOLUMNS (
    ADDCOLUMNS (
        'plan',
        "New Col",
        VAR salesValue =
            LOOKUPVALUE (
                sales[Sales],
                sales[id_store], [id_store],
                sales[id_department], [id_department],
                sales[date], [date]
            )
        RETURN
            IF ( ISBLANK ( salesValue ), [Planed], salesValue )
    ),
    "id_store", [id_store],
    "id_department", [id_department],
    "date", [date],
    "Sales Planed or Real", [New Col]
)

    Innerjoin-Challenge

     

    Best Regards,
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Great solution v-jiascu-msft. Thanks a lot.