Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm working on my first Power BI Project and am consistently running into issues as I learn the system.
I have a series of entries which I'm trying to break down by their dates, weekly, which of course is not normally a date hierarchy option (seriously, why?!). So, I've created a custom Date table within the project using DAX which includes the WEEKNUM column, and created the hierarchy, then trying to build the relationship to connect it but I must be doing that part wrong because once the relationship is applied the hierarchy (and entire "Date" classification, for that matter) for the "Open Date" column I'm trying to work with all disappear.
I've tried variants of the relationship cardinality but none seem to work - they either report circular dependancies or "work" with the date functionality removed. I'm sure it's something fairly simple I'm missing but I'm genuinely stuck here...
Solved! Go to Solution.
I'll try to give Kudos accordingly to all those who have helped but ultimately a combination of fixes were necessary:
1. Ensure there exists a Date table within the model that includes its own hierarchy with "Week" within it.
2. Separate the table of data into 2 tables, one for opened items and one for closed items
3. Ensure each table's Date column is either formatted as "Date" (rather than DateTime) or else create a duplicate column that is defined as Date only
4. Connect the two tables' "Date" formatted Date columns to the Date table within the model
5. Build the Visualisation, using the Count of the date only date columns of the two data tables as the column values, and the Date hierarchy as the shared axis - the data has come out perfectly.
I'll try to give Kudos accordingly to all those who have helped but ultimately a combination of fixes were necessary:
1. Ensure there exists a Date table within the model that includes its own hierarchy with "Week" within it.
2. Separate the table of data into 2 tables, one for opened items and one for closed items
3. Ensure each table's Date column is either formatted as "Date" (rather than DateTime) or else create a duplicate column that is defined as Date only
4. Connect the two tables' "Date" formatted Date columns to the Date table within the model
5. Build the Visualisation, using the Count of the date only date columns of the two data tables as the column values, and the Date hierarchy as the shared axis - the data has come out perfectly.
@PantherusNZ Create a common key to connect try using this dax
Key = datevalue(date[date)
then do it for table now create a relationship in between both table
output will be like this .
Hi, Thank you for your reply.
Apologies for being a bit slow here but I'm still learning - would I be raising that key reference for the new Date table or the table that I'm working with? If the latter, there are two separate columns containing date values that I'm trying to work with, would I have to do this for both?
Also, where would I enter that code line? Does it matter?
@PantherusNZ Hey Right just do it with your both table date table and fact table .
at later stages if you want to create another relationship with diffrent date then Userelationship
fulfill your requirement.
Kindly refer above Link for more info.
Hi,
OK so I've been hunting around the application and finally guessed where to enter the key line of code by creating a measure under the newly created Date table OR in the data table - but it is throwing errors every time I try, whether I try to refer it to its own Date column or the specific Open Date Only column in the data table.
Apologies for being a bit slow but I don't quite understand where I should be entering these commands since I'm clearly doing something wrong...
Hi @PantherusNZ,
Personally, I would have done the data modelling portion of this (including the date table) in Power Query, not DAX.
Unless you need the timestamp as a single column, there can be significant benefit in splitting the date and time into seperate columns to reduce high cardinality, especially in large data models.
This would also serve to give you suitable join columns as your date table will typically only go down to 'date' at the lowest grain and your newly split column is ready to join to your date dimension.
If you want to continue with the DAX version, I believe @HarishKM is referring to 'New Column' not 'New Measure'.
Hope this helps.
Regards,
Kim
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Hi @PantherusNZ ,
The date hierarchies will not work when there is one relationship(with multiple cardinality) on that field. Please review the content in the following links and check whether the methods in these links can help you resolve your problem.
Auto Date Hierarchies Gone after Adding Table/Join
Best Regards
Hello, thank you for your reply and links.
I've read through those discussions but don't understand how to apply the advice to what I'm trying to do. I had created the new Date table because I wanted to be able to add Week to the hierarchy, with it in place, even when marked as the date table, the hierarchies still are only Year/Quarter/Month/Day - then if I apply the relationship to try and actually connect them the hierarchy vanishes. So clearly I'm doing it wrong but I don't understand from those links what to actually do...
I think you are having trouble becuase the [Open Date] column in your 'Problems in the last 12 weeks data' table is a date/time. It is not able to find a matching entry in the Date column because of the time portion of the filed. You can convert it to just the date using INT in a new column.
Open Date Only = INT ( 'Problems in the last 12 weeks data'[Open Date] )
Then join that into your date table.
Hi, Where would I enter that bit of code?
I tried creating the date-specific column in data transforming to get everything aligned but it still refuses to acknowledge that connection.
In the table view you would add it as a new column.
Aha! OK, thank you - created the column.
I've recreated the Relationship to the custom date table and...no hierarchy.