cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chakrared
Regular Visitor

How to Make a Batch ID

Table 1 - 'Source L1 to L3' 

L1L2L3Batch ID...
value1anothervalue1andanothervalue1 
value2...etc...anothervalue2...etc...andanothervalue2...etc... 

All values are text

Table 2 -'L1Distinct'

L1L1Index
value11
value22...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! 

1 ACCEPTED SOLUTION

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

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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

View solution in original post

4 REPLIES 4
chakrared
Regular Visitor

This is an example of the table for visual help

Example of Batches.png

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

Table relationshipsTable relationships

wdx223_Daniel_0-1602574222466.png

wdx223_Daniel_1-1602574252076.png

 

hope this can help

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

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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Thank you so much. Works perfectly! 😁

Example of Batches_3.png

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors