Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all, I created a table in Power bi desktop, I want to highlight duplicate values in some columns and fill in the background color. Here I want to highlight "Material","Description","Vendor name" and "Plant" columns.
Solved! Go to Solution.
First create a measure to calculate the row count based on your criteria.
Row Count =
//returns 0 if there's no duplicate, otherwise 1
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT ( 'Table', 'Table'[Column1], 'Table'[Column2], 'Table'[Column3] )
) = 1,
0,
1
Add the columns to a table.
In fields in visualizations pane,
Only those with 1 Row Count will be highlighted.
Note: Conditional formatting is to be done for each field.
Proud to be a Super User!
Hi @caiying100126,
Any availed columns that can be used as index? If not I'd like to suggest you add it in query editor.
Then you can try to use the following measure formula to calculate row count and return color and use on conditional formatting features to highlight specific rows.
Measure =
VAR currIndex =
MAX ( Table[Index] )
VAR currItem =
SELECTEDVALUE ( Table[Column] )
VAR dc =
COUNTROWS (
FILTER ( ALLSELECTED ( Table ), [Column] = currItem && [Index] < currIndex )
)
RETURN
IF ( dc > 1, "Red", "Black" )
Regards,
Xiaoxin Sheng
First create a measure to calculate the row count based on your criteria.
Row Count =
//returns 0 if there's no duplicate, otherwise 1
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT ( 'Table', 'Table'[Column1], 'Table'[Column2], 'Table'[Column3] )
) = 1,
0,
1
Add the columns to a table.
In fields in visualizations pane,
Only those with 1 Row Count will be highlighted.
Note: Conditional formatting is to be done for each field.
Proud to be a Super User!
Create a measure like this. You have to do it for each column.
Color sales = if(countrown(Table)>1,"red","green")
Go to conditional formatting. Based on font or background choose advance control. There choose the option of field and use this measure
Also refer: https://www.mssqltips.com/sqlservertip/6265/power-bi-conditional-formatting-for-matrix-and-table-vis...
Refer to section: Power BI Field Value Conditional Formatting
User | Count |
---|---|
88 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |