Building dynamic matrix from tabular model

[Anonymous]

Hi all,

I have a question regarding building "dymanic" matrixes in a PBI report depending on intervals in a underlying table.

The easiest way to explain is showing you how the data is structured as of now.

First, we have a table (simplified in this picture) containing a list of matrixes depending on intervals of lengths:

Then we have a similar list of matrixes with intervals of diameters:

Last of all, we have the facts table (also a bit simplified):

What I am trying to accomplish is a report with a "dynamic" matrix somewhat looking like this:

The report should then sum up the Count and place it in a correct (from the interval) cell in the matrix where the columns would be from the intervals of diameters, and the rows would be from the intervals of lengths. Also, the user should easy be able to change the matrix shown from the slicer.

My normal solution for this would to actually let the fact-table have a Matrix-dimension, populated by the normal ETL-process, but that is not really applicable here I think. Since the facttable can contain x billion of rows, also the matrixes are on a "per user"-basis, so each user (around 1000 users) can have 1-20 matrixes, with means we might end up with mayn billions of rows.

Does anyone have a idea on if this is even possible within DAX/PBI, or is the only solution to redesign the facttable/ETL leaving is with a facttable with up to 100 billion rows?

[TomMartens]

Hey,

this is an interesting task, can you please upload an excel file that contains your sample data to onedrive or dropbox? This would be helpful to easily recreate a PBI model.

I'm wondering about the data in your fact data especially the rows 19 and 20 from the diameter it could be either Matrix 1 or Matrix 2. From the length it just fits to Matirx 1.

Does this mean it will be just Matrix 1

Regards

Tom

[Anonymous]

Hi!

Here is a link to the sample data:

https://b3it-my.sharepoint.com/:x:/g/personal/michael_holmstrom_b3it_se/Ecpo5uyrvRBDkqVEh1ndLZ4BQ4rq...

Regarding your question, those could be either in matrix 1 or in matrix 2. Regardless of which matrix the user selects in the slicer, those rows will be shown.