Table structure

[Anonymous]

Hi,

I'm working with data that will have about 10 million rows per day. Currently, the rows are nested which is something Power BI can't handle. The question that arises is the structure of the table. We have sessions where each sessions has multiple moments. We are trying to avoid the increase of rows, if we have 5 moments, then each session will have 5 rows with one type of moment per row. I know Power BI wants to work with rows and columns in a tabular format, but is there an optimal way to have for example one moment per column and at the same time have one session per row? Tables to try to explain it:

Session ID      Moment A   Moment B   Moment C

123A	30	50	20
231B	10	30	20
321C	90	40	50

Session ID     Moment   Time

123A	A	30
123A	B	50
123A	C	20
231B	A	10
231B	B	30
231B	C	20
321C	A	90
321C	B	40
321C	C	50

We want to be able to show the percentage of the total, so for session ID 123A Moment A, it's 30% of the total etc.

Thanks and Merry Christmas!
/Jari

[v-jayw-msft]

Hi @Anonymous ,

You will nedd to either unpivot the table and create one measure to get the percentage value or create multiple measures for each Moment.

Best Regards,

Jay

[amitchandak]

@Anonymous , the second one is ideal for you need but if you have 10 Million per day. Unpivot of this data will take huge time and may not work.

In that case if you have limited columns you can try like

divide(sum(Table[ColumnA]), sum(Table[ColumnA])+sum(Table[ColumnB])+sum(Table[ColumnC])+sum(Table[ColumnD])+sum(Table[ColumnE]))