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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hengsworld
Frequent Visitor

remove duplicates names

Hello Everyone, 

 

I been stuck on this problem for a bit now and reaching our for some help 🙂 

 

I am looking to remove duplicate names for example 

 

UsersName | Is-WindowFoundationReady 

Bob | Foundation Ready

Bob | Not Foundation Ready 

Yo | Not Foundation Ready 

Nick | Foundation Ready

 

Is-WindowFoundationReady = IF('Table'[Office]="Office 2016","Foundation Ready", IF('Table'[Office]="Office 365","Foundation Ready", IF('Table'[Office]="Office 2013","Not Foundation Ready")))

 

How can I remove the duplicates, once a user is foundation ready then they shouldn't appear in as Not foundation ready? 

 

Is-WindowFoundationReady = IF('Table'[Office]="Office 2016","Foundation Ready", IF('Table'[Office]="Office 365","Foundation Ready", IF('Table'[Office]="Office 2013","Not Foundation Ready")))

How can I remove the duplicates, once a user is foundation ready then they shouldn't appear in as Not foundation ready?

The result should list:
Users | Foundation Ready
Yo | Not Foundation Ready
Nick | Foundation Ready

9 REPLIES 9
Phil_Seamark
Employee
Employee

This table will give you a distinct list of users who have at least one record where they are Foundation Ready according to what I think your rules are.

 

Let me know what you think

 

New Table = SUMMARIZE(
                 FILTER(
                    'Table',
                    'Table'[Office] IN {"Office 365","Office 2016"}
                    ),
                    'Table'[User Name] , 
                    "Is-WindowsFoundationReady" , "Foundation Ready"
                    )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_SeamarkJust out of curiosity, where can I find DAX complex examples to play? I have referred your answers to many threads, most of them out from DAX solutions. Can you please kindly provide some inputs, how I will be mastered with DAX.

 

Any reference links? Any Books?

 

 

Hi @rocky09

 

I reckon the best source of complex examples to play with are here in the community.  There is a good variety in the types of problems posted and remember not every accepted answer is necessarily the best.  Take a few problems and look to see what other solutions you can come up with.  

 

Other than that, I think @Sean recommended the Definitive Guide to DAX the other day, and if he recommends it, it's probably pretty good.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark

Thank you.

Hi Phil, when I tried to implement the new table it is giving me this error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." 

 

 

Sean
Community Champion
Community Champion

Remove Duplicate Names.png

I see, thank you! @Phil_Seamarkhow can I also provide users that are not foundation ready? 

@Phil_Seamark  nice touch with the IN operator! Smiley Happy

so basically the above is equivalent to...

 

New Table ALT =
SUMMARIZE (
    FILTER (
        'Table',
        'Table'[Office] = "Office 365"
            || 'Table'[Office] = "Office 2016"
    ),
    'Table'[User Name],
    "Is-WindowsFoundationReady", "Foundation Ready"
)

 

I think the challenge there is that Bob will appear twice with two states (ready and not ready).  Bit that is just my interpretation, which can be miles off 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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