Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
Your requirement is still confusing, if the dataset and the expectation is as below
Please follow steps
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?
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.
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)
Thanks!
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.
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
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!
Thank you jahida for the formula.
The sales result is split based on the Branch as I mentioned in my first post.
Cheers,
Vivian
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
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 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
Your requirement is still confusing, if the dataset and the expectation is as below
Please follow steps
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,
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |