Group by and create custom name for values occuring mulitple times

[tomislav_mi]

Hey guys,

Hope all of you are keeping well!

I am looking for a solution that could be used both in Power BI and Excel Data model: 

Having a table like this

Account	Customer	Rep	Period
A	1	John	Month
B	1	John	Quarter
C	1	Rebecca	Year
T	2	Bob	Quarter
Z	2	Bob	Day
U	2	Bob	Quarter
E	3	Lewis	Month

I would need to group it by Customer column and get a table with a unique customer row like this
but I would like to use my custom name for values that occurs multiple time.

Customer	Rep	Period
1	Multi	Multi
2	Bob	Multi
3	Lewis	Month

 
Is that possible?

Thank you all a lot!

[amitchandak · Accepted solution]

@tomislav_mi , Create two measures like this and use with customer in visual

if(distinctcount(Table[Rep]) >1, "Multi",max(Table[Rep]) )

if(distinctcount(Table[Period]) >1, "Multi",max(Table[Period]) )

[amitchandak · Accepted solution]

@tomislav_mi , Create two measures like this and use with customer in visual

if(distinctcount(Table[Rep]) >1, "Multi",max(Table[Rep]) )

if(distinctcount(Table[Period]) >1, "Multi",max(Table[Period]) )

[tomislav_mi]

@amitchandak Thank you so much!

This works also, but I am trying to get it done as a calculated table or even at the stage of uploading the data in PowerQuery...

[v-kelly-msft · Accepted solution]

Hi @tomislav_mi,

You could create a calculated table as below:

Table 2 = 
SUMMARIZE('Table','Table'[Customer],"Rep",
var _num=CALCULATE(DISTINCTCOUNT('Table'[Rep]),FILTER(ALL('Table'),'Table'[Customer]=EARLIER('Table'[Customer])))
Return
IF(_num>1,"multi",MAX('Table'[Rep])),
"Period",
var _num=CALCULATE(DISTINCTCOUNT('Table'[Period]),FILTER(ALL('Table'),'Table'[Customer]=EARLIER('Table'[Customer])))
Return
IF(_num>1,"multi",MAX('Table'[Period])))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[tomislav_mi]

Thank you guys a lot!

@v-kelly-msft @amitchandak