Reply
Regular Visitor
Posts: 16
Registered: ‎07-13-2018
Accepted Solution

Date Table relationship issue

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.

1.JPG

 

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

2.JPG

 

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

 

3.JPG

 


Accepted Solutions
Advisor
Posts: 171
Registered: ‎11-18-2016

Re: Date Table relationship issue

[ Edited ]

Hi @Mhetre123

 

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

View solution in original post


All Replies
Regular Visitor
Posts: 22
Registered: ‎11-14-2017

Re: Date Table relationship issue

Hi- 

What do you mean doesn't work ? 

 

Check 

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

Regular Visitor
Posts: 16
Registered: ‎07-13-2018

Re: Date Table relationship issue

Hi @sailkitty

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.

Established Member
Posts: 143
Registered: ‎10-26-2018

Re: Date Table relationship issue

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

Highlighted
Regular Visitor
Posts: 16
Registered: ‎07-13-2018

Re: Date Table relationship issue

Hi @Nick_M

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 Smiley Sad

 

Date - No hierarchy.JPG

Established Member
Posts: 143
Registered: ‎10-26-2018

Re: Date Table relationship issue

[ Edited ]

hmm. that's wierd. Ill take a look

Advisor
Posts: 171
Registered: ‎11-18-2016

Re: Date Table relationship issue

[ Edited ]

Hi @Mhetre123

 

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

Established Member
Posts: 143
Registered: ‎10-26-2018

Re: Date Table relationship issue

Ah, beat me to it @richbenmintz Smiley Surprised

 

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

Regular Visitor
Posts: 16
Registered: ‎07-13-2018

Re: Date Table relationship issue

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

 

@Nick_Mthanks, that works too.