topic Re: Create missing records for missing dates in DAX Commands and Tips

Create missing records for missing dates

maclura — Tue, 25 Jan 2022 11:11:11 GMT

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

Re: Create missing records for missing dates

amitchandak — Tue, 25 Jan 2022 13:41:55 GMT

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/

Re: Create missing records for missing dates

AlexisOlson — Tue, 25 Jan 2022 21:22:24 GMT

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 )

Re: Create missing records for missing dates

maclura — Wed, 26 Jan 2022 09:10:59 GMT

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

Re: Create missing records for missing dates

maclura — Wed, 26 Jan 2022 09:16:39 GMT

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