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 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 NameClient Code
A1
B2
C3
D4
E refer to below table

  

Client NameBranchClient Code
E(i)5
E(ii)6
E(iii)7
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?

11 Replies

  • samdthompson's avatar
    samdthompson
    Memorable Member
    10 years ago

    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.

  • felyoubi's avatar
    felyoubi
    Kudo Collector
    10 years ago

    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 :smileyvery-happy:

     

    Let me know if this makes sense.

     

    Thanks,

    Fahd

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago

    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'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!

  • Tishchenko's avatar
    Tishchenko
    Responsive Resident
    10 years ago

    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.