Unique ID

---

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

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?