cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jonito
Helper I
Helper I

broken relation with date dax table

Hello,

 

I have a dax table as follow

Date = CALENDAR(DATE(2018,1,1),DATE(2030,12,31))

 

And a fact table with sales and report repiod (multiple with same time/date).

 

I made a link one to many but it seems don't work.

 

If i call values from calendar it doens't compute values (report time).

 

Whant i can do to fix this ?

 

Regards.

2 ACCEPTED SOLUTIONS

You really need to post an image of your model. You should not need USERELATIONSHIP. It is not clear if [report period] is a date. You should not be using the fact column inside SAMEPERIODLASTYEAR



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

I finaly added a segment with the Report[Report Period] and it works @MattAllington  !

I wonder how ? Did it regroupe the report ?

View solution in original post

5 REPLIES 5
Jonito
Helper I
Helper I

Hello Matt,

It doens't works even if i force the relas if i force this column usage.

I have simply changed the date on both sides to DD/MM/YYYY to test it.


Maybe i am doing wrong ? Total YTD LY return me an error with the following code:

Total YTD =
CALCULATE (
SUM(Report[Revenue USD]),
DATESYTD ( Report[Report Period] ),
USERELATIONSHIP (Report[Report Period],'Date'[Date] )
)

Total YTD LY =
CALCULATE (
[Total YTD],SAMEPERIODLASTYEAR(Report[Report Period] ),
USERELATIONSHIP (Report[Report Period],'Date'[Date] )
)

Error Message:
MdxScript(Model) (56, 17) Erreur de calcul dans la mesure 'Report'[Total YTD LY] :
La fonction « SAMEPERIODLASTYEAR » attend une sélection contiguë quand la colonne de date n'est pas unique, est discontinue ou contient une partie heure.


Regards.

You really need to post an image of your model. You should not need USERELATIONSHIP. It is not clear if [report period] is a date. You should not be using the fact column inside SAMEPERIODLASTYEAR



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Thank you for your reply,

 

i modified the code for both measures but i have the same amount for Total YTD LY and Total YTD (i should have less for YTD LY):

Total YTD =
CALCULATE (
SUM(Report[Revenue USD]),
DATESYTD ( Report[Report Period] )
)
Total YTD LY =
CALCULATE (
[Total YTD],SAMEPERIODLASTYEAR('Date'[Date] )
)

(I put some blank on other fields)

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

Report period (each client has his report with the same report period for each month):
Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Regards

 

 

I finaly added a segment with the Report[Report Period] and it works @MattAllington  !

I wonder how ? Did it regroupe the report ?

View solution in original post

Split the date/time column in the fact table into 2 columns, one for date, one for time. Then join on date



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors