Forum Discussion
PBI Model link not working
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.
- A table with some basic numbers (Metric Values (F))
- 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!
9 Replies
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.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!
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.
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!
