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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors