topic Re: How to Make a Batch ID in DAX Commands and Tips

How to Make a Batch ID

chakrared — Mon, 12 Oct 2020 15:19:26 GMT

Table 1 - 'Source L1 to L3'

L1	L2	L3	Batch ID...
value1	anothervalue1	andanothervalue1
value2...etc...	anothervalue2...etc...	andanothervalue2...etc...

All values are text

Table 2 -'L1Distinct'

L1	L1Index
value1	1
value2	2...etc...

L1 is text, L2 is an index value (Number) Values in L1 are related to L1 in 1st table and are only the DISTINCT values in that tables L1 column

Table 3 & 4 'L2Distinct' and L3Distinct' are same as 'L1Distinct' but for L2 and L3 columns

All I want to do is take the columns 'L1Index' on table L1Distinct, 'L2Index on table L2Distinct and 'L3Index' on table L3Distinct and present them as a sequence in the original table....

So if the numbers where 1, 3, 10 for example I'd like 1310 in the column.

I'm new to DAX and struggling to do this. Any help would be appreciated. (Or even advice on an easier way to make a Batch ID based on all distinct values in each column having the same number ?

Thanks!

Re: How to Make a Batch ID

chakrared — Mon, 12 Oct 2020 16:04:37 GMT

This is an example of the table for visual help

So for example in the last row I'd want to have 2614 as a Batch ID. Problem making this for me with n00b DAX is that these are numbers and that each of the columns is from three seperate tables.

Table relationships are like this: -

Re: How to Make a Batch ID

amitchandak — Tue, 13 Oct 2020 06:43:07 GMT

chakrared , Please try a new column in table Source L1 to L3

New column = related(L1Distinct[L1Index]) & related(L2Distinct[L2Index]) & related(L3Distinct[L3Index])

Re: How to Make a Batch ID

wdx223_Daniel — Tue, 13 Oct 2020 07:31:27 GMT

hope this can help

Re: How to Make a Batch ID

chakrared — Tue, 13 Oct 2020 08:38:15 GMT

Thank you so much. Works perfectly! 😁