Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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]) )
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 , 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 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...
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |