Forum Discussion
Hierarchy Dim and Fact - Issues with Counts by level
I am working on my first PBI project and struggling. My problem is with understanding how the hierarchy table should be related to the fact table, which is a list with all the 3 levels combined. My Hierarchy table includes the flattened hierarchy. I am trying to create measures(counts), visuals, slicers by level for each of different measurable fields in the fact. I am not sure If I have set up these hierarchy table correctly. I have a separate relationship from each level column in the hierarchy table to the primary key in the fact table (using USERELATIONSHIP). Not sure if this is the correct option also. I have reviewed so many resources and tried so many times examples. I really need some headway. For example level 1 is change type Epic, level 2, Change Review, level 3 Subtask. Any assistance is appreciated. Thanks
Can you try dropbox, google or another file hosting? There is a bit of complexity here that could be hard to explain through words.
In the mean time, I will try to replicatein a demo file.
11 Replies
- Tutu_in_YYC
Super User
In both dimension and fact, there will be a column that has the same key. It is unique. Its probably the same key that you use to create Level1/2/3 columns and the path ie. flattening the parent-child hierarcy.
Find that primary key of the dimension,create a relationship using that key column, and that should be all you need, just that one relationship.
In visualization, use the column Level1/2/3 to break down your measure.
Attached is a sample.Thank you for comfirming that the hierarchy table should only need the one relationship Hierchary[ISSUE_ID] and Fact[ISSUE_ID]. From you pbix, I could not determine how to create a measure for each level to count the number of issues by item type in the fact table.
- Tutu_in_YYC
You dont have to create a measure for each level. Just 1 measure, in your case is probably DISTINCTCOUNT(FactTable[Issue_ID])
And use the measure in the Values bucket of the matrix
The Hierarchy Dim[ISSUE_ID] to FactTable[ISSUE_ID] is 1:1. I created the measure and set up the visual as explained. The Date table[Date] has a relationship to the FactTable{CreateDate]. I'm only receiving the total count. I'm not sure what I need to change. I thought I needed to filter the facttable using the the level in the the Hierarchy Dim.
When I drill dow to level 2, 235 is the correct count for level 1
When I drill down to level 3, 755 is the total count for level 1 & 2(235+520). Level 3 count is 98
- Tutu_in_YYC
How would you like to see the end result/visualization? Try drawing it out for us.
- Tutu_in_YYC
Are you able to share your pbix, you can remove any sensitive data first. At this moment, its hard to validate your numbers without knowing your data.
If you say 235 should be the total for level 1, but it is showing 853, it could mean that you have tickets that is not mapping to issue ID, or something else.
Dim[ISSUE_ID] to FactTable[ISSUE_ID] is 1:1
set this to 1:many (many at facttable)
For some reason, I could not share the .pbix
Here are the columns from the flattened hierarchy in the hierarchy table, which I duplicated from the fact which has the same parent child relationship.
These counts are implicit count aggregations from the hierarchy dimension, which I am attempting to use the relationship between the fact and hierarchy for these counts from the fact table. I thought I could create a measure using the hierarchy depth to filter the level counts.
I am trying to create visuals based on these different fields in the fact table by level. My measures have been incorrect and I am unable to filter the fact table by filtering the hierarchy table.
- Tutu_in_YYC
Can you try dropbox, google or another file hosting? There is a bit of complexity here that could be hard to explain through words.
In the mean time, I will try to replicatein a demo file.
v-venuppuCommunity Support
Hi pwrbi7308 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
Regards,
Rama U.
- v-venuppu
Hi pwrbi7308 ,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.
