Formulas

[Milozebre]

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 :

Several references belonging to the same team and with the same number.

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.

[v-qiuyu-msft · Accepted solution]

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.

Best Regards,
Qiuyun Yu

[v-qiuyu-msft · Accepted solution]

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.

Best Regards,
Qiuyun Yu

[Milozebre]

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 😞

I realize that I still have some way to go 🙂

A big thank-you

[Phil_Seamark · Accepted solution]

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

[Milozebre]

Hi @Phil_Seamark

Thank you for your reply

Two solutions 🙂

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 🙂

Thank you

[MFelix]

Hi @Milozebre,

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

Regards,

MFelix

[Milozebre]

HI MFelix, 

Thank you for your reply.

I have already tried but it doesn't work.

[MFelix]

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