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
Anonymous
Not applicable

Create unique value (with some sort of transpose needed)

Hi All,

 

(This is my first post, so please correct me if I need to change anything. Thanks! :))

 

I am having an issue modelling data in my PBI model. It seems like a simple task, but I cannot get my head around it.

 

In short:

I would love to be able to link to the table using the field "Dimension ID", but this is only possible for me, if the value is unique.

 

In the linked excel file, I have tried to explain the issue with two tables;

  • The raw ERP data
  • and the end result which I am hoping someone can help me create in Power BI Desktop. 

Excel file with data example

 

I hope a bright mind will be able to guide me in the right direction. (Perhaps someone will even upload a .pbix file showing the steps needed to manipulate the data?)

 

For those who are interested:

This is a Dynamics NAV issue, where dimensions are stored as Dimension Set IDs to conserve space on the database (and to ensure performance), but this creates issues when people (me! :)) want to look across all dimensions of a given transaction. So if I'm able to create unique values of the dimension set IDs, I'll be able to slice across all dimensions. (which would be awesome! :))

 

Best

Martin

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

I'm going to call out to @ImkeF once again as I think that you should be doing that in Power Query.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

You're looking for a pivot-operation (without aggregation).

See file attached.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

I'm going to call out to @ImkeF once again as I think that you should be doing that in Power Query.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

You're looking for a pivot-operation (without aggregation).

See file attached.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

Sorry for the delayed feedback (haven't been able to log in for some time.)

 

This is perfect, and exactly what I was looking for! 

 

Thank you!

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.