Create missing records for missing dates

[maclura]

Hi,

I know there are many questions like this, but I don't have enough DAX to customize them to solve my problem.

In short,

I have this daily summary table

and I need to transform it like this

Until now I am just able to create a table which is the cartesian product of [ID] and my calendar [Date] with CROSSJOIN.
But I don't know how to associate the existing Val 1,2 and 3 to the proper record and populate the missing values with zeroes.

Thanks for any help.

maclura

[AlexisOlson · Accepted solution]

Once you have the cross join, one option to populate the columns is with a LOOKUPVALUE.

For example:

Val1 =
LOOKUPVALUE (
    Summary[Val1],
    Summary[ID], Transform[ID],
    Summary[Date], Transform[Date],
    0
)

[maclura]

Dear @AlexisOlson ,

LOOKUPVALUE just works great! It's the solution to my problem.

It also allows me to avoid to SUMMARIZE duplicate entries in the source table. This is great!

Thank you.

maclura

[amitchandak]

@maclura , Do a left join in power query( Date table should be first or on left) , create a date table in power query

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

https://www.mssqltips.com/sqlservertip/6756/power-bi-calendar-table/

DAX Joines. or related columns

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[maclura]

Thank you @amitchandak 
Even if this is not the solution to my problem, thanks to your suggested links, I refreshed a bit my knowledge on join types. Very interesting the article on DAX join with related or not related tables!

For my situation the CROSSJOIN (cartesian product of 2 tables) is the only solution to avoid losing "date" records.

Thank you for your input, you deserve a Kudo!

maclura