Unique ID

[buinia]

Hi guys,

I have encountered the following problem with unique value in Power BI table. I have a table of client names which are 99% unique and each of them gets assigned a client code which is used to load their data to our database. However 1 particular client has 3 client codes depending on the 3 brands under this client. I am having difficulty trying to create a table which only show the client code and client's data since the master client sheet which this table look up from only allows 1 unique client code assigned to each client.

I would really appreciate if someone could please suggest a solution to help me overcome this. Maybe a measure that will lookup both client name and client branch (when it comes to this particular client). Please note that I can't change the client name to split it into 3 different clients. Thanks

Client Name	Client Code
A	1
B	2
C	3
D	4
E	refer to below table

Client Name	Branch	Client Code
E	(i)	5
E	(ii)	6
E	(iii)	7

[Eric_Zhang · Accepted solution]

---

@buinia wrote:

In Excel this should be quite a straight forward formula of "if the Client Code is not blank, look up the Client Name and return Client Code but if it is blank look up both Client Name and Branch to return correct Client Code"

I just really struggle to put this is Power BI terms.

Thanks

Vivian

---

@buinia

Your requirement is still confusing, if the dataset and the expectation is as below

Please follow steps

1. create a proper relationship
   
2. The DAX expression for two columns
   

   Client Code2 = IF(ISBLANK(master[Client Code]),CONCATENATEX(branch,branch[Client Code],","),master[Client Code]&"")
   
   
   Sales2 = IF(ISBLANK(master[Client Code]),SUM(branch[Sales]),master[Sales])

[Tishchenko]

This problem because PowerBI Desktop modeling interface don't support very impotant functionality

that present in SSAS and in Excel PowerPivot like this (pictire from SSTD, PowerPivot has another UI):  

 

for CustomerID "Row Identifier" = true, for CustomerName "Keep Unique Rows = true"

I'm usually start create model in Excel, then in PowerBI Desktop do import this model from Exxel. and second - you can create KPI only in PowerPivot, PowerBI desktop grab KPI too.

[Sean]

I've been pushing this Idea every chance I get so here we go again

Everyone Please VOTE - so we can build KPIs inside PBI Desktop (so we don't have to build in PowerPivot and then import in PBI)

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6556753-ability-to-set-goals-and-...

Thanks!

[Eric_Zhang]

@buinia

Your requirement is not clear for me, are you looking for something as below?

If yes, then create a one to many relationship between those two tables and create a calculated column with

Client Code2 = IF(ISBLANK(Table24[Client Code]),CONCATENATEX(Table23,Table23[Client Code],","),Table24[Client Code]&"")

If it is not what you're after, please be more specific on your requirement.

[buinia]

Thank you for all your replies.

I think i should clarify the final result that I want to achieve is as below:

I have tried all your methods above but still cannot come to the result I wanted.

Vivian

[jahida]

Hi Vivian,

I think that clarification does help. If you want just the first two columns in the final table, you could use a formula like this (not beautiful but will get the job done):

Table 2 = UNION(
	SELECTCOLUMNS(FILTER('Table', 'Table'[Client Code] <> BLANK()), 
		"Client Name", 'Table'[Client Name], "Client Code", 'Table'[Client Code]),
	FILTER(SELECTCOLUMNS(CROSSJOIN(DATATABLE("Index", INTEGER, {{1}, {2}, {3}}), FILTER('Table', 'Table'[Client Code] = BLANK())),
		"Client Name", 'Table'[Client Name], "Client Code", PATHITEM(SUBSTITUTE('Table'[Client Code 2], ",", "|"), [Index])), [Client Code] <> BLANK())
)

This formula will scale to multiple companies with multiple Codes, but you have to hard-code the the maximum number of Codes a company can have in the DATATABLE function. If that requirement is unreasonable in your situation, I or someone else can provide you with a more generalized formula (and someone else can probably give you a cleaner one either way).

However, it's not possible to split the Sales number based on the info you gave in your last post. You need to give more information on how that should happen if you want help there.

Hope that helps!

[buinia]

Thank you jahida for the formula.

The sales result is split based on the Branch as I mentioned in my first post.

Cheers,

Vivian

[buinia]

In Excel this should be quite a straight forward formula of "if the Client Code is not blank, look up the Client Name and return Client Code but if it is blank look up both Client Name and Branch to return correct Client Code"

I just really struggle to put this is Power BI terms.

Thanks

Vivian

[jahida · Accepted solution]

Hi Vivian,

I think I get it a bit better now, although I might be way off. Here's what my dataset looks like now:

     

Here's the updated version of the formula I provided yesterday:

Table 2 = UNION(
	SELECTCOLUMNS(FILTER('Table', 'Table'[Client Code] <> BLANK()), 
		"Client Name", 'Table'[Client Name], "Client Code", 'Table'[Client Code], "Sales", 'Table'[Sales]),
	FILTER(ADDCOLUMNS(SELECTCOLUMNS(CROSSJOIN(DATATABLE("Index", INTEGER, {{1}, {2}, {3}}), FILTER('Table', 'Table'[Client Code] = BLANK())),
		"Client Name", 'Table'[Client Name], "Client Code", PATHITEM(SUBSTITUTE('Table'[Client Code 2], ",", "|"), [Index])), "Sales", LOOKUPVALUE('Table 3'[Sales], 'Table 3'[Client Name], [Client Name], 'Table 3'[Client Code], VALUE([Client Code]))), [Client Code] <> BLANK())
)

Which gives this table:

Does that answer the question?

[Eric_Zhang · Accepted solution]

---

@buinia wrote:

In Excel this should be quite a straight forward formula of "if the Client Code is not blank, look up the Client Name and return Client Code but if it is blank look up both Client Name and Branch to return correct Client Code"

I just really struggle to put this is Power BI terms.

Thanks

Vivian

---

@buinia

Your requirement is still confusing, if the dataset and the expectation is as below

Please follow steps

1. create a proper relationship
   
2. The DAX expression for two columns
   

   Client Code2 = IF(ISBLANK(master[Client Code]),CONCATENATEX(branch,branch[Client Code],","),master[Client Code]&"")
   
   
   Sales2 = IF(ISBLANK(master[Client Code]),SUM(branch[Sales]),master[Sales])

[felyoubi]

Hi,

Is the client code unique per Client Name? if yes, it's a one to many relationship depicted below:

Client Name	Client Id
A	1000
B	1001
C	1002
D	1003
E	1004

Branch Id	Branch Code	Client Id	Client Code
2001	(i)	1001	1
2002	(i)	1002	2
2003	(i)	1003	3
2004	(i)	1004	4
2005	(i)	1005	5
2006	(ii)	1005	6
2007	(iii)	1005	7

 If not, it becomes a many to many relationship where you have Client table, Branch table and a composite table that links the two. However, there is nothing wrong in having one table with repetitive records because it is what PBI does behind the scenes anyway 

Let me know if this makes sense.

Thanks,

Fahd

[samdthompson]

you need to create a separate table with unique client id's only and then join to it.you can the choose for example client id 'E' from the id table and then 'branch' from the existing table. 

to make the table:

on the modelling tab on the ribbon click new table; in the formula bar type time_table = summarize('table','table'[client_id]) - swap out the names for what ever in correct in your model. This will provide a remove duplicates type solution for all values in the table.