Want to use measures as rows and columns of a matrix

[dkrid]

Hello,

I know that Power BI doesn't allow measures to be used as rows or columns in a matrix but I seem to get stuck here. Any suggestions are very welcome.

Here is my simplified data:

I want to show "Item" in a grid with 3 columns (fixed value) in an ascending order, filling the grid from left to right, and from top to bottom like this:

 I decide to use a matrix and I prepare my data as follows:

    - Sort the "Item" column in an ascending order.

    - Add an index column "Idx1" starting from 0.

    - Add a column "Row1" = QUOTIENT(Idx1, 3)

    - Add a column "Col1" = MOD(Idx1, 3)

And here is a matrix with "Row1" as row, "Col1" as column, and "Item" as value:

 So far so good. Now I want to filter the data with "Category" so I add a "Category" slicer. But when I select a value from the slicer, say "P2", I get something like this:

The matrix shows only 2 columns (instead of 3) and there are blank cells inside the matrix. But what I want is something like this:

 I think I can solve this problem by adding columns that calculates the index at run-time, after a filter is selected.

     - New index column "Idx2" = RANKX(ALL(Data[Item]), CALCULATE(SUM(Data[Idx1])))

    - Then add column "Row2" = QUOTIENT(Idx2, 3)

    - and column "Col2" = MOD(Idx2, 3)

The resulting values look OK...

But these are measures and a matrix doesn't take these fields ("Row2" and "Col2") as rows or columns of a matrix. If I add these fields as columns, either I get wrong results or I get an error (like circular dependency error). At this point I have no idea how to proceed, or maybe there are other ways to do it. Any suggestions?

Thank you,

Krid

[v-xinruzhu-msft · Accepted solution]

Hi @dkrid 

You can create two new table:

Column = {0,1,2,3}
Row = {0,1,2,3,4,5}

Then create a measure in column table

Measure = var _t = ALLSELECTED('Table')
var _t2 = ADDCOLUMNS(_t ,"rankx", RANKX(_t ,[Item] ,,ASC,Dense)-1)
var _t3 =  ADDCOLUMNS(_t2 , "Row2" , QUOTIENT([rankx],3) ,"Col2", MOD([rankx],3))
var _row = MAX('Row'[Value])
var _column = MAX('Column'[Value])
var _t4 =  FILTER(_t3, [Row2] = _row && [Col2]=_column)
return 
IF( MAXX(_t4,[Item])<> BLANK(), MAXX(_t4,[Item]), BLANK())

Output:

Best Regards!

Yolo Zhu

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

[dkrid]

Hi v-xinruzhu-msft,

Thank you for the code. I have to admit I don't fully understand your code but it works! Now I am trying to learn how it works.

[alena2k]

Hi @dkrid 
You can use Microsoft Chiclet slicer visual to present data this way:  choose Horizontal orientation and limit to 3 columns. Visually it will give you result you need, maybe you can style or combine it with other visuals to complete your task?

[dkrid]

Hi alena2k,

Thank you for your suggestion. Chiclet slicer looks very nice. Unfortunately when I check the Chiclet slicer against other requirements of my project it just lacks one feature that I really need so I cannot use it.