Forum Discussion
Question for appending Names
Hi All,
I have a situation in PBI and would like your help on how to approach this.
The request is simple. I am showing two different names, which is the same person.
| Name | Products Sold |
| Peter | 100 |
| (Administrator) Peter | 500 |
Normally what I would do is remove (Administrator) in the query editor and it will look like this.
| Name | Products Sold |
| Peter | 600 |
The problem here is that I can not simply do that because the customer asked me to append the names and use the name which is currently being used. For example here is how it is now and how the end result needs to be:
| Name | Products Sold | ProductLastSold |
| Peter | 100 | 1-1-2020 |
| (Administrator) Peter | 500 | 20-1-2020 |
| Andrea | 200 | 1-9-2020 |
| (Local Admin Andrea) | 400 | 20-7-2020 |
| Name | Products Sold | |
| (Administrator) Peter | 600 | |
| Andrea | 600 |
Thank you very much for thinking with me!
11 Replies
Hi DeBIe ,
Why don't you do a dimension table with the connection between the current name and names you have something similar to this:
Name Current Name Peter (Administrator) Peter (Administrator) Peter (Administrator) Peter Andrea Andrea (Local Admin Andrea) Andrea then relate by name column with your other table and make the calculations with current name.
Only question here is that this table needs some maintenance.
Hi MFelix ,
that is one of the options I thought of, but I was wondering if it would really be needed to create a separate dimensional table to realize this. I feel like if this similar to this are going to happen, my datamodel will grow and grow and it will eventually not be maintainable anymore.
Hi DeBIe ,
Only question I have with this type of data is that you can have more than one rule to acommodate all the roles or small changes in the names, you would need a to have filters or measure or slicers and then it's the same ammount of work if not bigger than to have the relational table.
Just giving options
DeBIe ,
Try this measure:
_ProductsSold = VAR _name = SELECTEDVALUE('Table'[Name]) RETURN CALCULATE(SUM('Table'[Products Sold]), FILTER(ALL('Table'), FIND(_name, 'Table'[Name], 1, 0)))camargos88 thank you for your help. This measure will work, but I need to apply this to different pages as well. I am also using the name as a filter on same pages.
Can you show me the measure that you created for "Name"?
I need to create more tables by using the first column with the (most recent) name. Not only for products sold.For example I have a timetable and I also would like the (most recent) name as my first column.
