topic Re: New table based on existing one with all dates from calendar in DAX Commands and Tips

New table based on existing one with all dates from calendar

KaZeK — Tue, 20 Jun 2023 09:36:10 GMT

Dear Community,

I'd like to ask for support due to the fact I do not know how to find the solution.

I have two tables:

1. Date

2. List

Table Date contains full calendar. Table List contains Dates (only working days), NameID, Price of NameID and some Factor1 of NameID.

Important is that there are hundreds of NameID, Price and Factor is changing by days (values are not fixed and assign only to NameID - they are changing also for particular days).

My problem is that I do not have holidays in Date collumn in Table List.

I would like to create new table, lets call it Analysis Table, with collumn with all days (working days + holidays) and took all NameID, Price and Factor1 from table List. Of course all NameID values which will be assign to correct date with Price and Factor1 values. I'd like to have holydays with assigned NameID with empty Price and Factor1 collumns.

How to do it?

Awaiting your feedback.

Re: New table based on existing one with all dates from calendar

devanshi — Tue, 20 Jun 2023 10:00:21 GMT

Analysis Table = CALCULATE(SUMMARIZE('List','List'[NameID],'List'[Price],'List'[Foctor],"abc",RELATEDTABLE('Date'[Date']))

Re: New table based on existing one with all dates from calendar

KaZeK — Tue, 20 Jun 2023 10:24:54 GMT

Thanks for your response. But it doesn't work:

"The expression refers to multiple columns. Columns cannot be converted to a scalar value."

Re: New table based on existing one with all dates from calendar

tamerj1 — Tue, 20 Jun 2023 10:46:19 GMT

Hi KaZeK
Please try

Analysis = ADDCOLUMNS ( CROSSJOIN ( VALUES ( 'Date'[Date'] ), SUMMARIZE ( 'List', 'List'[Name_ID], 'List'[Foctor1] ) ), "Price", CALCULATE ( SUM ( 'List'[Price] ) ) )

Re: New table based on existing one with all dates from calendar

devanshi — Tue, 20 Jun 2023 11:04:26 GMT

Try this once ,

Analysis Table = CALCULATE(SUMMARIZE('List','List'[NameID],'List'[Price],'List'[Foctor],"abc",RELATED('Date'[Date']))

Re: New table based on existing one with all dates from calendar

KaZeK — Tue, 20 Jun 2023 12:12:18 GMT

Hello, thanks for support. It looks like there is some issue:

"This expression references a Relationship object named "f2cbd857-ad3f-4ea9-a12c-0e9bf782230f" which contains an error."

Where is the information in the formula that it should be linked by Date:
'List'[Date] <--> 'Date'[Date] ?

Maybe that's the problem?

Re: New table based on existing one with all dates from calendar

KaZeK — Tue, 20 Jun 2023 12:13:10 GMT

Hello, still the same issue

Re: New table based on existing one with all dates from calendar

KaZeK — Tue, 20 Jun 2023 12:20:14 GMT

After few modification I have similar warning:

"The expression refers to multiple columns. Columns cannot be converted to a scalar value."

Re: New table based on existing one with all dates from calendar

tamerj1 — Tue, 20 Jun 2023 12:49:53 GMT

KaZeK
Are you sure you are creating a calculated table?

Re: New table based on existing one with all dates from calendar

KaZeK — Tue, 20 Jun 2023 13:46:35 GMT

To be honest I copy paste your formula and Calculate function could be used. But like I said I need to "copy" names ID and values from List table where one date is assigned to hundreds NameID and Price and Factor1 values.

It means I have the same quantity of NameID & Price & Factor1 for one particular date.

Eg.
for 2023.06.20, I have:

100x NameID rows

100x Price rows

100x Factor1 rows

Date | Name ID | Price | Factor1

2023.01.01 | NameID_1 | Price_11 | Factor1_1

... | ... | ... | ...
2023.01.0n | NameID_n | Price_n | Factor1_n

I'd like to create the same table but with dates (weekends & holidays) which are missing in List table.

Re: New table based on existing one with all dates from calendar

tamerj1 — Tue, 20 Jun 2023 14:05:46 GMT

KaZeK
Please provide screenshots with illustration.

Re: New table based on existing one with all dates from calendar

devanshi — Wed, 21 Jun 2023 04:49:12 GMT

Try this ,

Analysis Table = CALCULATETABLE(SUMMARIZE('List','List'[NameID],'List'[Price],'List'[Foctor]),RELATED('Date'[Date']))