topic Re: Create Array (List) with DAX in DAX Commands and Tips

Create Array (List) with DAX

Anonymous — Fri, 06 Mar 2020 07:28:08 GMT

Hi There,

I have Different Customers [Column] in Different Years [Column]. Now I want to create a New Table I guess, with the Customer and a Array which includes the Years like this "2017, 2018, 2019" or "2017, 2019". Just if the customer was active in a year, i want to get the years in one row.

Is something like that possible?

Thanks a kind regards.

Sam

Re: Create Array (List) with DAX

Mariusz — Fri, 06 Mar 2020 08:06:04 GMT

Hi Anonymous

You can use CONCATINATEX()

CONCATENATEX( <table>, <expression>, [delimiter] )

https://docs.microsoft.com/en-us/dax/concatenatex-function-dax

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: Create Array (List) with DAX

Anonymous — Fri, 06 Mar 2020 09:56:02 GMT

Is this not an easy job for Power Query?

Best
D

Re: Create Array (List) with DAX

Anonymous — Fri, 06 Mar 2020 11:48:12 GMT

Hi darlove,

guess it's easy with M but not for an mere user like me :). Which formula shall I use to perform the output in power query?

Re: Create Array (List) with DAX

Anonymous — Fri, 06 Mar 2020 12:25:04 GMT

Put this M code into Advanced Editor in PQ and execute. You'll see how it's done.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLjFU0lEyMjAwUIrVgQgYoQtAVBiiq0ASMEbXYoxdhRG6ocYIAROIGUboAsZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, Year = _t]), #"Grouped Rows" = Table.Group(Source, {"Customer"}, {{"AllCustYears", each _, type table [Customer=text, Year=text]}}), #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "OnlyYears", each [AllCustYears][Year]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"AllCustYears"}), #"Added Custom" = Table.AddColumn(#"Removed Columns", "Years", each Text.Combine([OnlyYears], ",")), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"OnlyYears"}) in #"Removed Columns1"

Best

Re: Create Array (List) with DAX

Anonymous — Fri, 06 Mar 2020 15:33:54 GMT

Hi Mariusz,

i tried this Formula but with 4 Million Rows, it works really really slow (didn't finish at all after 30 Min). What is happening in this formula? And is it more capable than the M Formula when it's all about performance?

Re: Create Array (List) with DAX

Anonymous — Fri, 06 Mar 2020 15:34:21 GMT

Hi Anonymous,

I tried to get into this

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLjFU0lEyMjAwUIrVgQgYoQtAVBiiq0ASMEbXYoxdhRG6ocYIAROIGUboAsZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, Year = _t]),

but do i need this in whole?

Where does that "AllCustYears" come from?

#"Grouped Rows" = Table.Group(Source, {"Customer"}, {{"AllCustYears", each _, type table [Customer=text, Year=text]}}), #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "OnlyYears", each [AllCustYears][Year]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"AllCustYears"}), #"Added Custom" = Table.AddColumn(#"Removed Columns", "Years", each Text.Combine([OnlyYears], ",")), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"OnlyYears"})

Re: Create Array (List) with DAX

Mariusz — Fri, 06 Mar 2020 15:43:09 GMT

Hi Anonymous

I can imagine it will be, Anonymous power query solution probably will be a better option

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: Create Array (List) with DAX

Anonymous — Fri, 06 Mar 2020 16:16:40 GMT

Mate, this is a demonstration of how it should work in M. You take this, inspect this, understand it and then apply to your model.

Best
D

Re: Create Array (List) with DAX

Anonymous — Mon, 09 Mar 2020 22:47:48 GMT

Anonymous

thanks i guess i get into it. 2 questions left.

to avoid that all other columns are removed, must i add them to {"KundeID"}?

#"Group" = Table.Group(#"Add Column 2", {"KundeID"}, {{"GroupYear", each _, type table [KundeID=text, Jahr=text]}}),

And so far so good. Is there a way to reduced the result per row to a distinct value?

Thanks in beforehand.

Greetings Sam

Re: Create Array (List) with DAX

Anonymous — Tue, 10 Mar 2020 18:47:39 GMT

Hi there.

No. You have to create a reference to the original table, remove duplicates according to your rules, then perform the aggregation as above and then join back to the original table selecting the columns you want.

It's best to learn something about Power Query.

Best
D

Re: Create Array (List) with DAX

Anonymous — Wed, 11 Mar 2020 07:56:53 GMT

Anonymous

Jeah I made it, created a second table (in excel i needed to make another query) delete all columns except Customer and Year, make distincts with both columns. Than use your formula and it worked perfect (don't forget to set a relation).

#"Andere entfernte Spalten" = Table.SelectColumns(#"Add Column 2",{"KundeID", "Jahr"}), #"Entfernte Duplikate" = Table.Distinct(#"Andere entfernte Spalten"), #"Group" = Table.Group(#"Entfernte Duplikate", {"KundeID"}, {{"GroupYear", each _, type table [KundeID=text, Jahr=text]}}), #"Add Custom 1" = Table.AddColumn(#"Group", "OnlyYears", each [GroupYear][Jahr]), #"Removed Column 1" = Table.RemoveColumns(#"Add Custom 1",{"GroupYear"}), #"Add Custom 2" = Table.AddColumn(#"Removed Column 1", "Jahre", each Text.Combine([OnlyYears], ",")), #"Removed Column 2" = Table.RemoveColumns(#"Add Custom 2",{"OnlyYears"})

Thanks a lot for those hints :).