Forum Discussion
Create missing records for missing dates
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
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 )
4 Replies
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/
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
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 )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
