PBI Model link not working

[AdviceSeeker77]

Hi folks, 

I’m having trouble linking my fact table and my calendar table in the PBI report. The relationship is actually OK but some DAX functions (e.g. DATEADD) are not working while reffering to both tables. Can anyone help pls?

I have two tables.

1. A table with some basic numbers (Metric Values (F))
2. A calendar table (Calendar (D)) - the structure of this table is sort of multiplicated as I need to calculate certain Flags per various combinations of the following columns (CODE_PERIOD_FREQUENCY, ID_BUSINESS_UNIT, ID_UPLOAD_MODE)

• The relationship between these 2 tables is based on combined PK (ID_BUSINESS_UNIT + CODE_PERIOD_FREQUENCY + ID_UPLOAD_MODE + CODE_PERIOD_VALUE)
• 
• And while I work with simple measures the relationship is wokring fine --> Data displayed by the visual.
• Now I would like to calculate a measure that would give me the website visits for the previous month but I am guessing that due to the structure of the Calendar (D) table and the combined key used for the relationship with my Metric values (F) table this would require some advanced DAX syntax? I have tried some googling but nothing worked for me yet so I am hopping to get some advice here pls.
•  
• 
• when I actually use dates from my Metric Values (F) table the visual is working (picture below - upper chart) and I am able to see the values for the previous month - but I need to get this through the Calendar (D) table as I need to access (and use in Filters) some addition columns here (e.g. Flag_LDA_Date) and here the visual is not working (lower chart).
• 
• I am also attaching the .pbix file: https://drive.google.com/file/d/1w7k_xO1l4nT8lPCN19a88IrTFVC727kK/view?usp=sharing for your reference. 

I would be super grateful for any suggestions and recommendations, thanks guys!

[AdviceSeeker77]

Thanks @v-jingzhang for your effort! Much appreciated!

Basically I would be super greatful for any tips that would do the trick which I am after (as described in the threat) so... anyone? anything? 😄

Thank you!

[v-jingzhang]

Hi @AdviceSeeker77 

If you use 'Calendar (D)'[YearMonth] to replace 'Calendar (D)'[Date] in DATEADD function, you will get the same result as when using 'Metric Values (F)'[DATE_PERIOD_VALUE].

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[AdviceSeeker77]

Hi _Jing,

thank you very much for your quick catch! This helped me to work with the Date type columns to visualize the numbers: 

However if I am to use the "CODE_PERIOD_VALUE" from the very same table to visualize the numbers I am not able to:

Is there any way how to do this pls? The reason behind is that for some axis formatting purposes I would like to use the text field (CODE_PERIOD_VALUE) over the date field (YearMonth).

Thank you very much!

[v-jingzhang]

Hi @AdviceSeeker77 

It is because you are using HASONEVALUE in the measure while one CODE_PERIOD_VALUE may have multiple related YearMonth Values. In this case, HASONEVALUE returns FALSE and IF returns Blank(). Please use other functions to replace HASONEVALUE.

Usually when using Time Intelligence functions like DATEADD, the referenced date column is expected to have continuout unique date values. I would suggest adding a standard date table into your model and use date column from this standard date table in DATEADD functions. Connect your current calendar table to standard date table on date columns. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[AdviceSeeker77]

Thanks again _Jing for your quick response!

And sorry to bother you again! I have tried adding the Calendar dates (D) table using: 

And I linked the table to my previous Calendar (D) using date columns.

Now when I am trying to simplify the measure's DAX code I am still noticing some "strange" behaviour: 

I have removed the HASONEVALUE function and used the new Calendar dates (D) table to get the previous months values. On the lower right table I am using the YearMonth from the Calendar dates (D) table and the visual is working as expected. However on the lower left table I am using the YearMonth from the Calendar (D) table (sorry for the same column name) and the values for the previous months are already not visible... and what is "strange" is that the total is calculated correctly for the Website visits previous month.

Any ideals pls? Thanks!

[v-jingzhang]

@AdviceSeeker77 It makes me confused too...🤔 Can you share a new .pbix file?

[AdviceSeeker77]

Sure! I have updated the file under the original link: https://drive.google.com/file/d/1w7k_xO1l4nT8lPCN19a88IrTFVC727kK/view?usp=sharing 

Thanks!

[AdviceSeeker77]

Hi @v-jingzhang ,

just wanted to check if there is any progress by any chance pls?

Thanks!

[v-jingzhang]

Hi @AdviceSeeker77 

Sorry for the late response as I still haven't figured out the reason... 

I think it is probably caused by the relationship. When I replaced [Website visits] with MAX('Calendar dates (D)'[Date]) in a new measure, I found it also returns blank. It means that it didn't pass the valid filter to 'Calendar dates (D)'[Date] column to get the correct dates but got blank, so the measure returns blank. I don't know why this happens...

Regards

Jing