Unpivoting 50+ measures when original table has 7 Million rows and 150+ columns?

[Crowford99]

I generally unpivot measures to create a Measures Dimension. Do we have a turning point when it becomes ineffective and separate columns for measures are better? I am working on a dataset now that has 7 million rows and 150+ columns; 50 of them are measures that I could unpivot.

[v-pnaroju-msft]

Hi Crowford99,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Hi Crowford99,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thank you, @lbendlin , for your response.

Hi @Crowford99 ,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

Unpivoting transforms wide-format data (multiple columns for attributes) into long-format data (each attribute in a single column), enhancing analysis and visualization in Power BI.

To address the concerns, please refer to the following approach, which may help in resolving the issue.

1. Assess if unpivoting suits your analytical needs. If reports require measures as rows for calculations or visualizations, unpivoting can be beneficial.
2. With a dataset of 7 million rows, unpivoting may significantly increase row count and impact performance. Retain frequently used measures as separate columns when necessary.
3. If performance degrades, consider creating explicit measures instead of relying solely on unpivoted data for efficient calculations.
4. Validate results post-unpivoting by checking for duplicates and row increases. Compare performance before and after transformation to ensure smooth report loading and refresh rates.

Additionally, please refer to the links below for further guidance:

Measures in Power BI Desktop - Power BI | Microsoft Learn
Unpivot columns - Power Query | Microsoft Learn

If our response is helpful, kindly mark it as the accepted solution and provide kudos to assist other community members.

Thank you.

[lbendlin]

Please define what you mean by "measure". It has a different meaning in Power BI.

[Crowford99]

 I mean numerical columns that contain metrics ( Sales, Profit etc.) or values that I want to use in calculations in dax. 

[lbendlin]

Have a look at Calculation Groups - they offer a way to decide where the cutoff should be.

Ultimately it is your business scenario that dictates your data model design and normalizing level.