Question for appending Names

[DeBIe]

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!

[camargos88]

@DeBIe ,

You can apply the filter to the report level, so it applies to all pages.

[DeBIe]

@camargos88 

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.

[camargos88]

@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)))

 

[DeBIe]

@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. 

[MFelix]

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.

[DeBIe]

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. 

[MFelix]

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]

Hi @MFelix I do not understand what you are trying to say?

Problem with creating a dimensional table for the names is a option. But how would I create the dimensional table based on latest usernames?

The way I create dimensional tables now is first to duplicate. Then I remove the columns which I do not need and then I remove the duplicate (names). The original column where I created the duplicate from, has over 500.000 records and it will get more. That means that the dimensional table has to load this amount of data as well because it's a duplicate. Is there a better way to do this? 

[MFelix]

Hi @DeBIe ,

In your example you only present 2 names and 2 variations, however when I look at this type of data usually the variations are more than we can handle.

For example you get all this names:

Peter

(Administrator) Peter

Peter Administrator

Peter Admin

Admin Peter

PeterAdmin

Peter Charles

And so on the only question I have here is that Peter is always present in the names but even that can not be full proof.

I think using the dimension table to maintain the relationship between the various alias and the latest name would be a better approach than to have it reling on make a measure or calculated column that would need changes if for example instead of Peter one of your lines now shows up as P. Administrator.

For getting the names you can reference another query and that will only be uploaded once, don't you have any ID for the users? is only based on names?

[DeBIe]

Hi @MFelix 

thank you for your reply.

Indeed I do not have ID's in the datawarehouse. It would be possible to add them I think if really needed. I understand what you are trying to say with a lot of variations of usernames. Can you tell me more about the query reference that you are talking about where it will be updated once?

[MFelix]

Hi @DeBIe ,

I misslead you abotu the referencing part, because this will lead to duplication on the refresh has you have, the option is to create a dataflow that will only be pickup once. Check article below.

https://docs.microsoft.com/en-us/power-bi/guidance/power-query-referenced-queries

Without having any other insigth on what is the struture of your data is difficult to have a clear path. Did you try to make a summarization table using dax?

If you don't have ID how do you know that the names Peter and Peter (Administrator) are the same?