Forum Discussion

hengsworld's avatar
hengsworld
Frequent Visitor
9 years ago

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

Need Help

9 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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"
                    )
    • Sean's avatar
      Sean
      Community Champion
      9 years ago

      Phil_Seamark  nice touch with the IN operator! :smileyhappy:

      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"
)

       

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        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 :)

    • hengsworld's avatar
      hengsworld
      Frequent Visitor
      9 years ago

      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." 

       

       

    • rocky09's avatar
      rocky09
      Solution Sage
      9 years ago

      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?

       

       

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        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.