Forum Discussion

buinia's avatar
buinia
Frequent Visitor
10 years ago
Solved

Unique ID

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 us...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago
    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])
  • jahida's avatar
    jahida
    10 years ago

    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?

buinia's avatar
buinia
Frequent Visitor
10 years ago

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's avatar
jahida
Impactful Individual
10 years ago

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!