Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Combining text data from 3 different databases

Hi all,   I am stuck with combining the data of three datasets. I've been looking around for similar cases, but haven't found what I'm looking for yet. I'm afraid I can't share the file due to the ...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

 
v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Ahhhhh, I didn't realise I was trying with the calculated measure button! Works like a charm now. Thanks for your patience and helping me out :)