Date Table relationship issue

[Anonymous]

Date Table relationship issue

https://1drv.ms/u/s!AhVscPhmToJ0k7gcVSus_llQdKYNKQ

When I use date-time column (CreatedOn.1) in existing table, column chart visual is working well.

I created new table using calenderauto() function along with 1 to many relationship.

Hierarchy is removed from CreatedOn.1 column which is great but with new Date column visual does not work.

[Anonymous]

Hi- 

What do you mean doesn't work ? 

Check 

(1) is [CreatedOn.1] in take time format 

[Anonymous]

Hi @Anonymous

As shown in in snapshot above message, it does not work in column chart. Value is shown as one single column in chart.

[CreatedOn.1] has data type - Date/Time.

[Anonymous]

the Date in the chart is still showing as a hierarchy ( which is why you can drill down).  Change that to just show the Date and not the Date Hierarchy and should work

[Anonymous]

Hi @Anonymous

Its not picking up Values. Thats why chart is not showing columns for each dates.

I tried it with and without Hierarchy options, not working 😞

[richbenmintz · Accepted solution]

Hi @Anonymous

your createdOn.1 Column contains time, when you join to the Date table you are not matching any of the dates there, can you create a column that is just the date portion in your source system or through power query?

You will find your pbix file updated with a calc column that joins to the date table to slices your data by date, I would not suggest joining on calc dax columns and would try to push creating column work to your source system or powery query

https://1drv.ms/u/s!AhCeuF2piSWMgcBRvlzvUfBvxtNL5A

I Hope this helps,

Richard

[Anonymous]

Ah, beat me to it @richbenmintz 

anyhow, if you fix the created on column to:

Column = FORMAT('1 pune'[CreatedOn.1],"MM/DD/YYYY")

and join that to the date table, it will work

[Anonymous]

Hey @richbenmintz I just extracted date portion in power query and it works!

@Anonymousthanks, that works too.

[Anonymous]

hmm. that's wierd. Ill take a look