cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Milozebre Member
Member

Formulas

I need your help to solve a problem. I have tried various solutions that I have read on the forums but that does not help me.

I have this kind of data :

 

Classeur1 - Excel.jpg
Several references belonging to the same team and with the same number.

New Message - Microsoft Power BI Community - Google Chrome.jpg

 

Dont add all numbers but only 1 times by ID

And I would like to get this kind of results via a matrix.

 

thank you in advance for your help.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Formulas

Hi @Milozebre,

 

You can create a calculated table to list all distinct ID, Team values:

 

Table = SUMMARIZE('Table1',Table1[ID],'Table1'[Team],"Value",MAX('Table1'[NUMBER]))

 

Then drag that [ID] and [Team] to matrix Row group, [Value] to Values.

 

q1.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Phil_Seamark Super Contributor
Super Contributor

Re: Formulas

Another option is to use the Query Editors remove duplicates function. Just select the 4 columns and click remove duplicates

 

Remove duplicates.png

 

Remove duplicates3.png


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

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Formulas

Hi @Milozebre,

Do a distincr count on ID and you will get what you need.

Regards,

MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Milozebre Member
Member

Re: Formulas

HI MFelix, 

 

Thank you for your reply.

I have already tried but it doesn't work.

 

Super User
Super User

Re: Formulas

The distinct count was done in the ID column or on the nber column?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Moderator v-qiuyu-msft
Moderator

Re: Formulas

Hi @Milozebre,

 

You can create a calculated table to list all distinct ID, Team values:

 

Table = SUMMARIZE('Table1',Table1[ID],'Table1'[Team],"Value",MAX('Table1'[NUMBER]))

 

Then drag that [ID] and [Team] to matrix Row group, [Value] to Values.

 

q1.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Phil_Seamark Super Contributor
Super Contributor

Re: Formulas

Another option is to use the Query Editors remove duplicates function. Just select the 4 columns and click remove duplicates

 

Remove duplicates.png

 

Remove duplicates3.png


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

Proud to be a Datanaut!

View solution in original post

Milozebre Member
Member

Re: Formulas

Hi @v-qiuyu-msft

First thank you for your reply.

 

This is exactly the result I was hoping for.

I hadn't thought of SUMMARIZE Smiley Sad

I realize that I still have some way to go Smiley Happy

 

 

A big thank-you

Milozebre Member
Member

Re: Formulas

Hi @Phil_Seamark

 

Thank you for your reply

 

Two solutions Smiley Happy

I am a taker because I am fond of learning and I realize that with the many features of Power Bi and DAX, I have enough to take care of for a long time Smiley Happy

 

Thank you

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 55 members 1,221 guests
Please welcome our newest community members: