way to dynamically change table’s rows and their granularity in Power BI

[Draszor]

Hi All, 

I am searching for efficient solution of the following problem:

Having an extended table consisting of many descriptive columns (for example: product, product group, customer, country of order, city of order etc) I would like to create a table visual (or matrix) and slicer, fulfilling the below conditions:

 - slicer - containing all descriptive dimensions of the extended table and being set for multi-choice

- table - containing one (or more) column with measure (being for instance Sum of Amount or sum of Quantity), and another column that would show content of dimensions that I click in the slicer (to the left of measure column).

the key functionality I look for:

I would like my table to behave like that:

1. if I choose in the slicer the Product, then my table shows me the Amount split per products

2. If, keeping product still chosen in my slicer, I click the "City", THEN SECOND COLUMN IS ADDED TO MY TABLE, WITH CITY. so that chosing Product AND city in my slicer, I get product and city as columns in the table and then I get the amount for each valid combination of those two

3. clicking 3rd dimension in the slicer, I get 3rd column in my table - Product-City and for instance customer)

etc. 

I've found some ways for creating dynamic tables, however none of those did propose solution so simple and elegant like the above. 

for now, I use quite extensivelu the dynamic tables, however I have two slicers with all dimensions (with only single choice) and I always have 2 columns for the chosen dimensions content in my table. quite a limitation. 

I was using previously QlikView, and up there this was doable (with some non standard formulas) and worked just as described above. hope the same is possible for PBI>

thanks for your answers

[Draszor]

Hello, I come back to this issue. I am addicted from working in click --> get way, and I miss this functionality a lot in PBI matrixes or tables. To overcome this problem I use the trick shown in the attached video LINK

the problem is, that to be able to dynamically add 3 columns, I need to create 3 unpivoted tables in PBI Query, and have 3 single-selection slicers with all dimensions in the report. 

I aim for having just one slicer with all dimensions (column names) in the report, and by multi sepecting those dimensions, to get them in the matrix/table. 

Any idea how that could be done?

[parry2k]

@Draszor Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Draszor]

Hi @parry2k 

thank you for your coments. 

As adviced, I wrote a simple report with manually entered data to ilustrate the functionality I use now (tab Dynamic Table) and this one I would like to have (tab What I would like to get)

Here is the LINK to my pbi file

hope now it is more clear what I would like to achieve. 

BR

[v-lionel-msft]

Hi @Draszor ,

Is this what you want?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Draszor]

hi, 

No, it is not. 

I would like to get the following interaction:

1. I mark Acc G 0 in the slicer. RESULT: I see one column with Acc G 0 content + one column with values in my matrix

2. having Acc G 0 marked, I mark Acc G 1 in the slicer. RESULT, I see 2 columns next to ech other, with the content of Acc G 0 and Acc G 1 + column with values

3. keeping those two Attributes marked, I choose a third one (Cost Center for instance). RESULT - now I see 3 columns with the content of Acc G 0, Acc G 1 and Cost center + column with values (for each combination of atributes)

etc.