Forum Discussion
Combining text data from 3 different databases
Hi Anonymous ,
We can create a calculated colmun using following formula to meet your requirement:
Column = VAR n = [Article number] VAR t1 = FILTER ( 'Article description', 'Article description'[Article number] = n && 'Article description'[Language] = "English" ) VAR t2 = FILTER ( 'Article database', 'Transaction data'[Article number] = n ) VAR articlename = IF ( COUNTROWS ( t1 ) > 0, MAXX ( t1, [Article name] ), MAXX ( t2, [Article name] ) ) RETURN n & " " & articlename
BTW, pbix as attached.Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
We can create a calculated colmun using following formula to meet your requirement:
Column =
VAR n = [Article number]
VAR t1 =
FILTER (
'Article description',
'Article description'[Article number] = n
&& 'Article description'[Language] = "English"
)
VAR t2 =
FILTER ( 'Article database', 'Transaction data'[Article number] = n )
VAR articlename =
IF (
COUNTROWS ( t1 ) > 0,
MAXX ( t1, [Article name] ),
MAXX ( t2, [Article name] )
)
RETURN
n & " " & articlename
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That is very helpfull v-lid-msft , thanks a lot for your support! Clever way to solve this. In your dataset it works like a charm, but I'm afraid I can't get it working in my own dataset.
The problem is caused by this part:
VAR n = [Article number]
I can't select [ Article number]. The warning I receive when manualy referring says that the column that it refers to contains too many values and no aggregation such as minimum, maximum, amount or sum is determined to get a single result. Any idea how to solve this? If I understand correctly it is because each article number is included multiple times in the article number table (which makes sense because article numbers are sold multiple times and for different prices in different periods or areas).
Furthermore, would it be possible to explain how the part below works? I do know how these separate functions work, but I don't get why you would use these specific commands in this particular case.
VAR articlename =
IF (
COUNTROWS ( t1 ) > 0,
MAXX ( t1, [Article name] ),
MAXX ( t2, [Article name] )
)Does COUNTROWS (t1) >0 determine if t1 (the 'article description' database) includes information about the article name/ is blank? And why do you need the MAXX function in this case? It is a text value, so why would a text have a max value?
Hi Anonymous ,
The formula should works fine when add a calculated column within Transaction data Table. Even this table has multi same article number, the VAR n = [Article number] will run for row, which means get only one article number from each row.
the logic of article name variable is to find name in Article description Table first if there are a english name for this article number, if there are not, it will find name in the Article database Table, the purpose of using MAXX function is to get only one value from a variable table, if there are multi name in the filted variable table, it will return result by alphabeta order for text type.
variable table t1 is the filted Article description Table, and the variable table t1 is the filted Article database Table, all of both have only the rows contain the english article number n, so using maxx will get the name, it will get the same value using lookupvalue or value, such as following formula:
Column 2 = VAR n = [Article number] VAR nameInDescription = LOOKUPVALUE ( 'Article description'[Article name], 'Article description'[Article number], n, 'Article description'[Language], "English" ) & "" VAR articlename = IF ( nameInDescription <> "", nameInDescription, LOOKUPVALUE ( 'Article database'[Article name], 'Article database'[Article number], n ) ) RETURN n & " " & articlename
BTW, pbix as attached.Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks for the extensive explanation v-lid-msft, whole formula makes sense now :).
I'm afraid that I haven't found a solution for this though:
I can't select [ Article number]. The warning I receive when manualy referring says that the column that it refers to contains too many values and no aggregation such as minimum, maximum, amount or sum is determined to get a single result. Any idea how to solve this?
I've been trying various things, but whatever I do, I still can't select [Article number ] from my transaction table to define 'n'. In the formula field the variable appears grey and if I hold my mouse above it, powerbi says "Can't find name [Article number]". Among other things I've already tried to alter the formula by editing it to 'Transaction data'[Article number], but that didn't help. I also tried to transform the data type from 'Text' to 'Whole number' but that didn't make any difference.
So close, but still not there :/. Any ideas?
Edit: I have two transactions that are not coupled to a specific article number due to a sytem bug. They did generate sales, but the article number cell is blank in those two transactions. Might this cause the error?
Edit2: If I change the first part to VAR N = MAXX('Transaction data';'Transaction data'[article number]) the error is gone and the highest article number + name is displayed correctly. Something in [article number] seems to cause an error that prevents the formula from working.
Hi Anonymous ,
Looks like you're trying to create a measure instead of a calculated column. If you use the same exact code, but as a calculated column, we can have the right result as following.
But if you are looking for a measure solution, you have correct the formula using maxx, also you can use the following code,
Measure =
VAR n = MAX('Transaction data'[Article number]) VAR nameInDescription = LOOKUPVALUE ( 'Article description'[Article name], 'Article description'[Article number], n, 'Article description'[Language], "English" ) & "" VAR articlename = IF ( nameInDescription <> "", nameInDescription, LOOKUPVALUE ( 'Article database'[Article name], 'Article database'[Article number], n ) ) RETURN n & " " & articlename
BTW, pbix as attached.Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.