Creating Matrices in Report Builder that are Similar to Power BI

[nidquival]

Good day,

I am trying to create a paginated report that has a matrix with a dynamic number of rows. I have tried both Microsoft Power BI Desktop and Microsoft Power BI Report Builder, and I would like to ask if there is a way that I can replicate the design that I made in Power BI Desktop in Report Builder. Both files used Matrices as the visuals.

Power BI Desktop

 As shown above, the matrix is visualized similarly to a table, wherein the values are grouped by dates and shown as one row. As I need to export the values to a PDF file, having incomplete results due to the presence of a scroll bar when printing and not having the ability to print all the rows is unacceptable.

Power BI Report Builder 

When the report was recreated in Power BI Report Builder, the values do not line up in one row similar to that in Power BI Desktop. 

Is there a step that I missed or a process that I should do in order to properly recreate the matrix in Report Builder such that the values line up in one row according to date?

Thank you very much.

Nico

[v-jingzhang · Accepted solution]

Hi @nidquival 

You don't need to add four columns [IN AM], [OUT AM], [IN PM], [OUT PM] into the table for the matrix visual. There is an easier way if you add a conditional column into the table to tell the meaning of TNAEvent number 0,1,2,3. (E.g. Type column in the following images.)

In Power BI Desktop,

Put Date in Rows, Type in Columns and Time in Values.

The same in Report Builder:

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[nidquival]

Thank you @v-jingzhang, it worked!

[amitchandak]

@nidquival , I doubt in report builder date is not getting aggregated. Just check the aggregation for the date in the report builder. In Power matrix values are always aggregated

[nidquival]

@amitchandak I checked the groupings. The matrix is grouped by Date. The values, however, do not seem to be placed in just one row. 

Here is the matrix that needs to be fixed. The Date is the parent group, while the IN_AM, OUT_AM, IN_PM, and OUT_PM are the values.

Do note that the values IN_AM, OUT_AM, IN_PM, OUT_PM are derivations from another value, DateTime.

Also, upon checking the matrix grouping properties, the parent group has a grouping based on the Date value.

Aggregating the values resulted in an error since, according to Report Builder, the values have filters.