Forum Discussion
DAX return incorrect value with both Row Hierarchy and Column Hierarchy
DAX & PowerBI Experts,
I'm trying to create a simple matrix view like a pivot table, where it returns correct value for each row and column intersection. Note here that for each row, there's hierarchy. So if it's at the top level, it sorts of like a subtotal. The same thing with column. It seems that I can get the Row Hierarchy dollar amount to show correctly, but going across it doesn't.
I tried for 2 days and I really don't know what went wrong with my star schema and/or even my DAX. So would appreciate if you could help me out.
It's my 1st time putting file in dropbox. Hopefully you'd be able to download it.
https://www.dropbox.com/s/2ydm91y2h0hdd1b/RowHierarchy%20with%20ColumnHierarchy.pbix?dl=0
Here's the issue that I have:
- Slicers didn't have the relationship with another slicer.
- i.e. when I click on the 'Phase slicer', the other slicer such as 'MPC', 'Product' and/or 'Model Code' didn't filter to the respective values corresponding to the selected 'Phase' slicer.
- Table Matrix showing the same issue with Slicer.
- Table Matrix showing incorrect data and amount.
- i.e. Product Type D30 showing Model Code of ETH which shouldn't be the case as Model Code ETH would be categorize as Product Type T21.
What I'd like to accomplish with this is exactly like Excel's pivot table where it can give me the correct value for any intersection of row hierarchy and column hierarchy. The reason I'm doing via PowerBI is because I have another factTable [ProformaPF] that is a completely different data setup as [5YP].
1 Reply
- lbendlinSuper User
You need to let go of the Excel mindset. Remove all the slicers and use the filter pane instead, let your (great!) data model do the work. Show fewer items. Beware the blue "i" ! It tells you when you are trying to do too much.
See attached.