cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mhetre123 Regular Visitor
Regular Visitor

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
richbenmintz
Advisor

Re: Date Table relationship issue

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

8 REPLIES 8
sailkitty Regular Visitor
Regular Visitor

Re: Date Table relationship issue

Hi- 

What do you mean doesn't work ? 

 

Check 

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

Mhetre123 Regular Visitor
Regular Visitor

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.

Super User
Super User

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

Mhetre123 Regular Visitor
Regular Visitor

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

Super User
Super User

Re: Date Table relationship issue

hmm. that's wierd. Ill take a look

Highlighted
richbenmintz
Advisor

Re: Date Table relationship issue

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

Super User
Super User

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

Mhetre123 Regular Visitor
Regular Visitor

Re: Date Table relationship issue

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

 

@Nick_Mthanks, that works too.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 194 members 2,009 guests
Please welcome our newest community members: