topic DAX CONCATENATE where conditions is from another table in DAX Commands and Tips

DAX CONCATENATE where conditions is from another table

HeiJoe — Wed, 31 Aug 2022 10:42:49 GMT

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?

Re: DAX CONCATENATE where conditions is from another table

Fowmy — Wed, 31 Aug 2022 10:54:47 GMT

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

Re: DAX CONCATENATE where conditions is from another table

tamerj1 — Wed, 31 Aug 2022 12:07:12 GMT

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 ) )

Re: DAX CONCATENATE where conditions is from another table

HeiJoe — Wed, 31 Aug 2022 11:36:56 GMT

Here is picture from the relationship.

Re: DAX CONCATENATE where conditions is from another table

tamerj1 — Wed, 31 Aug 2022 11:52:18 GMT

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?

Re: DAX CONCATENATE where conditions is from another table

HeiJoe — Wed, 31 Aug 2022 11:56:55 GMT

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

Re: DAX CONCATENATE where conditions is from another table

tamerj1 — Wed, 31 Aug 2022 12:04:41 GMT

Hi HeiJoe
Please try this calculated column

Re: DAX CONCATENATE where conditions is from another table

HeiJoe — Wed, 31 Aug 2022 12:15:46 GMT

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.

Re: DAX CONCATENATE where conditions is from another table

tamerj1 — Wed, 31 Aug 2022 12:23:21 GMT

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?

Re: DAX CONCATENATE where conditions is from another table

HeiJoe — Wed, 31 Aug 2022 12:38:52 GMT

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

Re: DAX CONCATENATE where conditions is from another table

tamerj1 — Wed, 31 Aug 2022 12:46:05 GMT

HeiJoe

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

Re: DAX CONCATENATE where conditions is from another table

tamerj1 — Wed, 31 Aug 2022 13:15:38 GMT

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?

Re: DAX CONCATENATE where conditions is from another table

HeiJoe — Wed, 31 Aug 2022 13:19:43 GMT

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.

Re: DAX CONCATENATE where conditions is from another table

tamerj1 — Wed, 31 Aug 2022 13:27:45 GMT

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 ) ) )

Re: DAX CONCATENATE where conditions is from another table

HeiJoe — Thu, 01 Sep 2022 08:09:36 GMT

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?

Re: DAX CONCATENATE where conditions is from another table

tamerj1 — Thu, 01 Sep 2022 08:36:11 GMT

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

Re: DAX CONCATENATE where conditions is from another table

HeiJoe — Thu, 01 Sep 2022 09:10:09 GMT

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?

Re: DAX CONCATENATE where conditions is from another table

tamerj1 — Thu, 01 Sep 2022 09:38:17 GMT

HeiJoe

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