Static and dynamic columns in matrix together

[mora11]

Hello All,

Is there any way to create matrix visual with few levels of hierarchy in rows and with 5 static columns and another 5 dynamic columns that will be displayed based on slicer selection in cooperance with Analysis Services?

Is there a way to sort values for columns that are created using SWITCH statement?

Thanks,

[Sergii24]

Hi @mora11, you can use "Filed parameter" to achiveve desired result:

1. Create Field parameter with 3 columns you want to select dynamically
2. Create a table visual and add there 5 static columns
3. Add the sixth column (your field parameter). 
4. Add filter parameter as slicer to the canvas

Now when selecting any value of field parameter in slicer, the number of column will dymamically change from 6 to 8 columns (because if none value is seelcted by the field parameter, all values are selected, so min selection of the field parameter is 1 column).  

You can learn more about "Filed parameter" here - https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

[mora11]

Hey, thanks for that but unfortunately that solution will not work in my case as I need to show data calculated on the fly (I need to switch usung measures instead of columns). 😞

[v-yanjiang-msft]

Hi @mora11 ,

According to your description, I create a sample to test.

Here's my steps:

1.Create two tables.

Column table:

Slicer table:

2.Create a measure:

Measure =
SWITCH (
    MAX ( 'Column'[Column] ),
    "Static", SUM ( 'Table'[Value] ),
    SUM ( 'Table'[Value] ) * SELECTEDVALUE ( Slicer[Slicer] )
)

Get the result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

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

[mora11]

Yaaay, it is possible. Thanks. But, as I can see native sorting option for dynamic and static columns is disable in that case, right? Is there a way to enable it again?

[v-yanjiang-msft]

Hi @mora11 ,

I'm not quite clear about what you mean "sorting option for dynamic and static columns". Do you mean sort the visual in More options>sort by>Columns?

If so, it doesn't make much sense, because values are sorted in the vertical direction。

Or if you want to sort the Column, for example in the above image, Static in the left and Dynamic in the right, simply add an index column in PowerQuery and select sort column by index.

Then get the result:

Best regards,

Community Support Team_yanjiang

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

[mora11]

Sorry for unclear description. Let's imagine that I have percentage values in the column (it doesn't matter if it is static or dynamic one). In regular matrix, report user can click on the column header and sort it descending. If user clicks again then values will be sorted ascending - it is regular PBI behaviour. Using dynamic columns in matrich, regular sorting option doesn't work at all (please check in PBIX that you shared). This is my case 🙂

[v-yanjiang-msft]

Hi @mora11 ,

By my test, Columns in matrix can't be sorted even if static one. Below is a simple matrix with data from the same table. When I click each column, it can only highlight the column instead of sort the values.

Only the Rows "Sub-category" can do that.

Please confirm again. Thank you!

Best regards,

Community Support Team_yanjiang

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

[mora11]

Yes, this is exactly what I meant.

[v-yanjiang-msft]

Hi @mora11 ,

Have you sorted the column in matrix in other sample but failed in this one?

Best regards,

Community Support Team_yanjiang

[mora11]

I would say that ability to sort columns in matrix visual is regular Power BI behaviour however it does not work with dynamic columns and this is why I am asking for advice.