DAX CONCATENATE where conditions is from another table

[HeiJoe]

Hi.

I'm making tracking URLs, so that when clicking url customer is redirected straight to spesific url. 

I have the parcels in table "dw parcels" and the conditions in column "r04puins" in table "dw lahetystilasto".

What I have tried so far as column and as measure:

URL for tracking =
IF('dw lahetystilasto'[r04puins] = "Posti",
CONCATENATE("https://www.posti.fi/fi/seuranta#/lahetys/",'dw parcels'[parcelno]),
IF('dw lahetystilasto'[r04puins] = "DB Schenker np",
CONCATENATE("https://ng.myschenker.fi/npseuranta/?id=", 'dw parcels'[parcelno]),
IF('dw lahetystilasto'[r04puins] = "Pakettipiste",
CONCATENATE("https://pakettipisteseuranta.r2web.fi/?tid=", 'dw parcels'[parcelno]),
IF('dw lahetystilasto'[r04puins] = "DHL",
CONCATENATE("https://www.dhl.com/fi-fi/home/tracking/tracking-express.html?submit=1&tracking-id=", 'dw parcels'[parcelno]),
IF('dw lahetystilasto'[r04puins] = "DB Schenker",
CONCATENATE("https://eschenker.dbschenker.com/app/tracking-public/?refNumber=", ''dw parcels'[parcelno])
)))))

Also tried this:

URL for tracking = SWITCH([r04puins],
"Posti", CONCATENATE("https://www.posti.fi/fi/seuranta#/lahetys/",'dw parcels'[parcelno]),
"DB Schenker np", CONCATENATE("https://ng.myschenker.fi/npseuranta/?id=", 'dw parcels'[parcelno]),
"Pakettipiste", CONCATENATE("https://pakettipisteseuranta.r2web.fi/?tid=", 'dw parcels'[parcelno]),
"DHL", CONCATENATE("https://www.dhl.com/fi-fi/home/tracking/tracking-express.html?submit=1&tracking-id=", 'dw parcels'[parcelno]),
"DB Schenker", CONCATENATE("https://eschenker.dbschenker.com/app/tracking-public/?refNumber=", 'dw parcels'[parcelno]),
"Matkahuolto", CONCATENATE("https://www.matkahuolto.fi/seuranta?parcelNumber=", 'dw parcels'[parcelno]),
" " )

Both are giving error that "can't find parcelno" or the syntax is wrong etc.

Any tip how I should proceed with this?

[tamerj1 · Accepted solution]

@HeiJoe 
Ok. Please try the following code for a calculated table > New Table

Addresses Table =
GENERATE (
    'dw parcels',
    SELECTCOLUMNS (
        RELATEDTABLE ( 'dw lahetystilasto' ),
        "URL for tracking",
            VAR r04puins = 'dw lahetystilasto'[r04puins]
            VAR parcelno = 'dw parcels'[parcelno]
            RETURN
                SWITCH (
                    r04puins,
                    "Posti", "https://www.posti.fi/fi/seuranta#/lahetys/" & parcelno,
                    "DB Schenker np", "https://ng.myschenker.fi/npseuranta/?id=" & parcelno,
                    "Pakettipiste", "https://pakettipisteseuranta.r2web.fi/?tid=" & parcelno,
                    "DHL",
                        "https://www.dhl.com/fi-fi/home/tracking/tracking-express.html?submit=1&tracking-id=" & parcelno,
                    "DB Schenker", "https://eschenker.dbschenker.com/app/tracking-public/?refNumber=" & parcelno
                )
    )
)

[HeiJoe]

I was able to create the calculated table, but seems that I can't make relationship from that table to 'dw lahetystilasto'? I got error "Circular depency was detected". Should that even be possible, to make relationships with calculated table?

[tamerj1]

@HeiJoe 
Why do you need the relationship? Everything you need is one table.

[tamerj1]

@HeiJoe 

Then we can add the URL column to the table. Otherwise you need to create this table in power query 

[HeiJoe]

If user selects one order, there should be only those parcel URL visible. How should the user know which URL are connected that spesific order?

[tamerj1]

Hi @HeiJoe 
What is the relationship between the two tables? If 'dw parcels' is at the one side of a one to many relationship with 'dw lahetystilasto', the you can use the following. Otherwise please provide your criteria for filtering 'dw parcels'[parcelno]

URL for tracking =
CONCATENATEX (
    RELATEDTABLE ( 'dw parcels' ),
    VAR r04puins = 'dw lahetystilasto'[r04puins]
    VAR parcelno = 'dw parcels'[parcelno]
    RETURN
        SWITCH (
            r04puins,
            "Posti", "https://www.posti.fi/fi/seuranta#/lahetys/" & parcelno,
            "DB Schenker np", "https://ng.myschenker.fi/npseuranta/?id=" & parcelno,
            "Pakettipiste", "https://pakettipisteseuranta.r2web.fi/?tid=" & parcelno,
            "DHL", "https://www.dhl.com/fi-fi/home/tracking/tracking-express.html?submit=1&tracking-id=" & parcelno,
            "DB Schenker", "https://eschenker.dbschenker.com/app/tracking-public/?refNumber=" & parcelno
        ),
    UNICHAR ( 10 )
)

[HeiJoe]

Here is picture from the relationship. 

[tamerj1]

@HeiJoe 
So for each shipmentno you have multiple parcelno. Which one one of them would you like to retrun? The last one i.e. the max one?

[HeiJoe]

All parcelno's from each shipmentno, so I can get URL for all parcel's.

[tamerj1]

Hi @HeiJoe 
Please try this calculated column

URL for tracking =
CONCATENATEX (
    RELATEDTABLE ( 'dw parcels' ),
    VAR r04puins = 'dw lahetystilasto'[r04puins]
    VAR parcelno = 'dw parcels'[parcelno]
    RETURN
        SWITCH (
            r04puins,
            "Posti", "https://www.posti.fi/fi/seuranta#/lahetys/" & parcelno,
            "DB Schenker np", "https://ng.myschenker.fi/npseuranta/?id=" & parcelno,
            "Pakettipiste", "https://pakettipisteseuranta.r2web.fi/?tid=" & parcelno,
            "DHL", "https://www.dhl.com/fi-fi/home/tracking/tracking-express.html?submit=1&tracking-id=" & parcelno,
            "DB Schenker", "https://eschenker.dbschenker.com/app/tracking-public/?refNumber=" & parcelno
        )
)

[HeiJoe]

That almost did it. Just that is should be in own rows, not in one long strin with all URLs. It works when there is only one parcelno in one shipment.

[tamerj1]

@HeiJoe 

I have edited the code adding UNICHAR (10). If you want completely separate rows then you need to create a new calculated table. Please let me know if this option is acceptable for you?

[HeiJoe]

Yes, I need those completely separate rows. Any tip for that?

[tamerj1]

@HeiJoe 
Sorry I'm a little confused. originally you got an error that says "can't find parcelno" which means that you were trying to create a column in the 'dw lahetystilasto' table. Now the last screenshot shows that you are creating the column in the 'dw parcels' table. Please confirm which columns do you want to see in the new calculated table?

[HeiJoe]

That picture was not from table, it was from visualizations. "Url for Tracking" is in "dw lahetystilasto". I need to see shipmentnumber, parcelno and that "Url for Tracking" in one table.

[tamerj1]

@HeiJoe 

Yes sure. I'll get back to you shortly. 

[Fowmy]

@HeiJoe 
Are you creating a Measure or a Calculated Column?
Ensure you have the column 'dw parcels'[parcelno] in the filter context

You may share a a screenshot