Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Celborn
Regular Visitor

Highlight non-duplicate if data from two columns

Hello,

 

I have monthly reports of account memberships in security groups and I am trying to figure out how highlight non-duplicate data.

 

MonthAccount NameSecurity Group

Nov

bobadmin
Decbobadmin
Novjakeexchange admin
Decjakeexchange admin
Decbillyexchange admin

 

I am trying to get it to highlight the last row since it is the unique data

 

I was trying (without luck)

 

unique = COUNTROWS(
FILTER(ALL(Data),
Data["account Name"],Data["Security Group"] =! EARLIER(Data["account Name],Data["Security Group"])
)
)

 

any help would be appreciated

1 ACCEPTED SOLUTION
Bifinity_75
Solution Sage
Solution Sage

Hi @Celborn ,try this:

 

- Create a calculate column:

Concat_ = 'Table'[Account Name]&'Table'[Security Group]

 

- Create this measure:

Color = if(CALCULATE(count('Table'[Concat_]),
ALLEXCEPT('Table','Table'[Concat_]))=1,"red","black")

- Put the measure in conditional formatting for your table:

Bifinity_75_0-1671056594289.png

 

- The result:

Bifinity_75_1-1671056628418.png

 

Best regards

 

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

This calculated column formula should work

Is it unique? = calculate(countrows(Data),filter(Data,Data[Account Name]=earlier(Data[Account name])&&Data[Security Group]=earlier(Data[Security Group])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

This calculated column formula should work

Is it unique? = calculate(countrows(Data),filter(Data,Data[Account Name]=earlier(Data[Account name])&&Data[Security Group]=earlier(Data[Security Group])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
PaulDBrown
Community Champion
Community Champion

Try:

Highlight non duplicate =
IF (
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Account Name], 'Table'[Security Group] )
    ) = 1,
    "Light Green"
)

result.jpgSample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Bifinity_75
Solution Sage
Solution Sage

Hi @Celborn ,try this:

 

- Create a calculate column:

Concat_ = 'Table'[Account Name]&'Table'[Security Group]

 

- Create this measure:

Color = if(CALCULATE(count('Table'[Concat_]),
ALLEXCEPT('Table','Table'[Concat_]))=1,"red","black")

- Put the measure in conditional formatting for your table:

Bifinity_75_0-1671056594289.png

 

- The result:

Bifinity_75_1-1671056628418.png

 

Best regards

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.