topic Re: When values duplicate then green when unique then Red in DAX Commands and Tips

When values duplicate then green when unique then Red

Iam_Syed — Thu, 19 Feb 2026 19:48:54 GMT

Hi Team,

I am stuck for creating a calculation but lo luck, please help on this.

In this table has Row = Category (Text)

Column = Type (Text)

Values = sum of sales (Numeric)

I want to show this table values when duplicate then Green color if Unique the Red color.

Category	S1	S2	S3
Mobile	113	113	114
Laptop	1133	1133	1134
Desktop	2211	1122	2211
Furniture	1101	1102	1101

Thanks

Syed

Re: When values duplicate then green when unique then Red

Irwan — Thu, 19 Feb 2026 22:51:28 GMT

hello Iam_Syed

please check if this accomodate your need.

1. i assumed your original table looks like the one you screenshot above, then you need to unpivot your table.

2. create a measure for checking the duplicate and coloring

Color Palette =
var _Duplicate = 
CALCULATE(
    COUNT('Table'[Category]),
    FILTER(
        ALL('Table'),
        'Table'[Category]=SELECTEDVALUE('Table'[Category])&&
        'Table'[Value]=SELECTEDVALUE('Table'[Value])
    )
)
Return
IF(
    _Duplicate>1,
    "Green",
    "Red"
)

3. create a matrix visual then assign the measure for conditional formating.

4. in conditional formating, choose Field value in Format style then use the measure for the field.

Hope this will help.
Thank you.

Re: When values duplicate then green when unique then Red

Iam_Syed — Fri, 20 Feb 2026 06:08:49 GMT

Hi Irwan,

Thanks for quick reply,

When I apply this calculation in my original table, all rows getting green,

Color Palette =

var _Duplicate =

CALCULATE(

COUNT('SDSA047 Sales History Analysis (As Reported) - PBI'[Profit Center]),

FILTER(

ALL('SDSA011 High Level Analysis (as Reported) - PBI'),

'SDSA011 High Level Analysis (as Reported) - PBI'[Profit Center]=SELECTEDVALUE('SDSA011 High Level Analysis (as Reported) - PBI'[Profit Center])&&

'SDSA011 High Level Analysis (as Reported) - PBI'[INV VAL]=SELECTEDVALUE('SDSA011 High Level Analysis (as Reported) - PBI'[INV VAL])

)

Return

IF(

_Duplicate>1,

"Green",

"Red"

)

Thanks

Syed

Re: When values duplicate then green when unique then Red

Irwan — Fri, 20 Feb 2026 06:24:29 GMT

Hello Iam_Syed

The logic should be fine.

Can you share a screenshot of your result where you said all green?

Thank you.

Re: When values duplicate then green when unique then Red

Iam_Syed — Fri, 20 Feb 2026 10:14:31 GMT

Hi Irwan,

Anyways I got the calculation in Copilot where I need to create 3 measures,

1 Rounded 2 Count of Duplicates 3 Color flag. thanks you can close this.

Re: When values duplicate then green when unique then Red

Iam_Syed — Mon, 23 Feb 2026 05:37:42 GMT

need to show in subtotal

Re: When values duplicate then green when unique then Red

FreemanZ — Sat, 21 Feb 2026 15:09:21 GMT

hi Iam_Syed ,

Not sure if i fully get you, supposing you have a data table like:

try to plot your expected matrix visual and apply conditional formatting for the value field background based field value with a measure like below:

Measure = VAR _cat = MAX(data[category]) VAR _type = MAX(data[type]) VAR _table = ADDCOLUMNS( SUMMARIZE( FILTER( ALL(data), data[category] = _cat && data[type] <> _type ), data[category], data[type] ), "Amt", CALCULATE(SUM(data[sales])) ) VAR _AmtList = SUMMARIZE(_table, [Amt]) VAR _result = IF([SalesAmt] IN _AmtList, "Green", "Red") RETURN _result

it works like:

Re: When values duplicate then green when unique then Red

Irwan — Sat, 21 Feb 2026 22:27:23 GMT

hello Iam_Syed

i see your result has already good, it shows green for same value while red shows the different value. except the zero and blank value.

here i made a little bit changes with some blank and zero value.

- previous (exact same DAX as before), blank and zero show green.

- changes (add more filter). the additional syntax is colored red in DAX below.

Color Palette =
var _Duplicate = 
CALCULATE(
    COUNT('Table'[Category]),
    FILTER(
        ALL('Table'),
        'Table'[Category]=SELECTEDVALUE('Table'[Category])&&
        'Table'[Value]=SELECTEDVALUE('Table'[Value])&&
        not (
        'Table'[Value]=0||
        ISBLANK('Table'[Value])
        )
    )
)
Return
IF(
    _Duplicate>1,
    "Green",
    "Red"
)

Hope this will help.

Thank you.

Re: When values duplicate then green when unique then Red

Iam_Syed — Mon, 23 Feb 2026 05:38:45 GMT

Hi Irwan,

We need that 0 values the table it selft will calculate right, if duplicate then Green or If unique the Red. that is fine what we expected,

I wanted to show the subtotal as well

Re: When values duplicate then green when unique then Red

techies — Mon, 23 Feb 2026 18:55:23 GMT

Hi Iam_Syed Based on the current logic (see screenshot attached), the subtotal is highlighted green when that category contains duplicate values across types.

Is this the expected behavior?

Re: When values duplicate then green when unique then Red

Irwan — Mon, 23 Feb 2026 22:48:58 GMT

hello Iam_Syed

for subtotal, just tick either column or row subtotal or both depend on your need.

Thank you.

Re: When values duplicate then green when unique then Red

v-kpoloju-msft — Fri, 27 Feb 2026 09:38:37 GMT

Hi Iam_Syed,

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to Irwan, techies, FreemanZ, for those inputs on this thread.

Has your issue been resolved? If the response provided by the community member Irwan, techies, FreemanZ, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

Re: When values duplicate then green when unique then Red

v-kpoloju-msft — Thu, 05 Mar 2026 05:39:47 GMT

Hi Iam_Syed,

Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.