Forum Discussion
Getting static row and corresponding value for row header in matrix
I wanna achieve the matrix where i can put the review status for each vendor in each state in a row instead of column, for eg johnSmith is a valid and approved vendor in NY for productAlphasoftware, so review status "Approved" should come under NY, similarly for other vendors. Kindly suggest how it can be achieved. Attached is screenshot of data modeling, and excel sheet(for refernce , need to get that grey line)
and #superusers
Hi NIBZZ ,
I have completed the following steps:
First, ensure your data in Power BI is structured with the necessary tables, such as:
- Product (Product Name, Category, Price, etc.)
- Orders (OrderID, CustomerName, ProductKey)
- Order Approval (OrderID, ApprovalStatus)
- State Review (Vendor, StateCode, ReviewStatus)
Next, establish these relationships in Model View:
- Product Table to Orders Table: ProductKey to ProductKey
- Orders Table to State Review Table: Vendor to Vendor
- Orders Table to Order Approval Table: OrderID to OrderID
After loading your data, configure the Matrix visualization as follows:
- Rows: Vendor (from the State Review table)
- Columns: StateCode (from the State Review table)
- Values: ReviewStatus (from the State Review table)
To format the Matrix:
- Keep Vendor Names Static: Ensure row headers are visible in the Format pane under “Row Headers.”
- Display State Columns: State names will appear as column headers, showing each vendor's review status per state.
- Disable Subtotals: In the Format pane, turn off Subtotals for a clearer layout.
Optionally, you can add filters and slicers:
- Add a slicer for Vendor or State by selecting the slicer icon and dragging the relevant field into the Values section. This allows you to filter data dynamically by vendor or state.
Finally, save your report via File > Save, and publish it to the Power BI service if you wish to share it.
Thank you,Tejaswi
13 Replies
Hi Aburar_123 , posted the expected solution
Put this measure in value of matrix
Review Status Display = Try this or use MAX instead of SELECTEDVALUE SELECTEDVALUE ( 'State Review'[ReviewStatus] )Hi,
Your question is not clear. Share the download link of an MS Excel file with your formulas/Pivot Tables there. I will try to translate that logic into DAX measures.
Use a Matrix visual in Power BI.
Set Rows to Vendor and ProductName.
Set Columns to StateCode or StateName.
Create a DAX measure like this:
Review Status by State =
CALCULATE(
MAX('State Review'[ReviewStatus]),
ALLEXCEPT('State Review', 'State Review'[StateCode], 'State Review'[OrderID])
)
- Use this measure as Values in the matrix.
- Turn off stepped layout and enable word wrap for clean formatting.NIBZZ Hey,
Matrix Visualization Steps:Data: Ensure columns for Vendor, State, Review Status.
Visual:
Select Matrix in Power BI. Configure: -
Rows: Vendor -
Columns: State -
Values: Review Status Format:
Add grid lines,
use conditional formatting for status highlights.
additonal dax
ReviewStatusByState =
CALCULATE(
LASTNONBLANK('State Review'[ReviewStatus], 1),
ALLEXCEPT('State Review', 'State Review'[StateCode])
)Thanks
Harish M
Hi NIBZZ,
Please let us know if you have had an opportunity to review the information provided. Should you have any further questions, please feel free to contact us.
Thank you.Hi NIBZZ ,
I have completed the following steps:
First, ensure your data in Power BI is structured with the necessary tables, such as:
- Product (Product Name, Category, Price, etc.)
- Orders (OrderID, CustomerName, ProductKey)
- Order Approval (OrderID, ApprovalStatus)
- State Review (Vendor, StateCode, ReviewStatus)
Next, establish these relationships in Model View:
- Product Table to Orders Table: ProductKey to ProductKey
- Orders Table to State Review Table: Vendor to Vendor
- Orders Table to Order Approval Table: OrderID to OrderID
After loading your data, configure the Matrix visualization as follows:
- Rows: Vendor (from the State Review table)
- Columns: StateCode (from the State Review table)
- Values: ReviewStatus (from the State Review table)
To format the Matrix:
- Keep Vendor Names Static: Ensure row headers are visible in the Format pane under “Row Headers.”
- Display State Columns: State names will appear as column headers, showing each vendor's review status per state.
- Disable Subtotals: In the Format pane, turn off Subtotals for a clearer layout.
Optionally, you can add filters and slicers:
- Add a slicer for Vendor or State by selecting the slicer icon and dragging the relevant field into the Values section. This allows you to filter data dynamically by vendor or state.
Finally, save your report via File > Save, and publish it to the Power BI service if you wish to share it.
Thank you,Tejaswi
