Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JustDavid
Frequent Visitor

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:

  1. 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.
  2. Table Matrix showing the same issue with Slicer.
  3. 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].

RowHierarchy with ColumnHierarchy Incorrect Values.png

 

1 REPLY 1
lbendlin
Super User
Super 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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.