topic Re: Print the column values multiple times using DAX in DAX Commands and Tips

Print the column values multiple times using DAX

Anonymous — Fri, 17 Jul 2020 02:24:36 GMT

Hi there,

I am trying to print values for a specific column. for example the column name is ABC and it is occured 18 times for a key, the output needs to be as per below.

Cheers,

Re: Print the column values multiple times using DAX

amitchandak — Fri, 17 Jul 2020 02:54:34 GMT

Anonymous , Can you explain the logic of output, not able to connect.

Re: Print the column values multiple times using DAX

Anonymous — Fri, 17 Jul 2020 03:01:14 GMT

Hi Amit,

Basically I want to concatenate the output the number of times it appears as per the column headers.

Hopefully this explains !

Cheers,

Re: Print the column values multiple times using DAX

Anonymous — Fri, 17 Jul 2020 03:13:41 GMT

amitchandak

Does the above answer help you to understand the problem?

Cheers,

Re: Print the column values multiple times using DAX

AntrikshSharma — Fri, 17 Jul 2020 04:21:04 GMT

This looks like a job for Power Query and not DAX

Re: Print the column values multiple times using DAX

Anonymous — Fri, 17 Jul 2020 04:24:27 GMT

Hi AntrikshSharma

A power Query solution will work too.

Cheers,

Re: Print the column values multiple times using DAX

AntrikshSharma — Fri, 17 Jul 2020 04:28:22 GMT

Unfortunately I am not capable enough in PQ, you can post your question here were PQ champs hangout:

https://community.powerbi.com/t5/Power-Query/bd-p/power-bi-services

Re: Print the column values multiple times using DAX

Anonymous — Fri, 17 Jul 2020 04:29:53 GMT

AntrikshSharma Thanks!!

Re: Print the column values multiple times using DAX

Anonymous — Fri, 17 Jul 2020 10:52:11 GMT

// Assumption is that you've got a table T // where the structure is more or less this: // Key | Column // ---------------- // 1 | ABC // 1 | ABC // 2 | XYZ // 2 | XYZ // 2 | XYZ // ... // From your sample it looks like a key // can only have one distinct value in // the Column column. [Output] = IF( // make sure that only one Key is // visible in the current context // and it's filtered directly. HASONEFILTER( T[Key] ), // The below will only work correctly // if the assumption above is in force. // Otherwise, SELECTEDVALUE will return // BLANK() and you'll end up with a string // of delimiters only or just an empty // string. var __numOfVals = COUNTROWS( T ) var __columnVal = SELECTEDVALUE( T[Column] ) var __ouput = CONCATENATEX( GENERATESERIES(1, __numOfVals, 1), __columnVal, ":|" ) return __output )

And, of course, [Output] is a MEASURE, not a calc column.

Best

Re: Print the column values multiple times using DAX

Anonymous — Sat, 18 Jul 2020 08:47:47 GMT

Thanks Anonymous The below measure works well.

I want to add one more column which is a unique number for each "ABC" value.

Cheers,

Rutika

Re: Print the column values multiple times using DAX

lbendlin — Sat, 18 Jul 2020 09:45:19 GMT

What's wrong with using REPT() ?

Re: Print the column values multiple times using DAX

Anonymous — Sat, 18 Jul 2020 10:22:27 GMT

lbendlin I need a delimter also to seperate the text

Re: Print the column values multiple times using DAX

lbendlin — Sat, 18 Jul 2020 10:25:26 GMT

Include the delimiter in the call to REPT and throw away the last character of the result.

Re: Print the column values multiple times using DAX

Anonymous — Sat, 18 Jul 2020 16:24:41 GMT

lbendlin

CONCATENATEX does it all for you automatically, so why bother?

Best
D

Re: Print the column values multiple times using DAX

lbendlin — Sat, 18 Jul 2020 17:00:04 GMT

Because it's fun, and an opportunity to test a function I didn't even know until very recently.

Re: Print the column values multiple times using DAX

Anonymous — Sat, 18 Jul 2020 17:44:46 GMT

Yeah... It's also fun to kill a bird with a cannon 🙂

Best
D

Re: Print the column values multiple times using DAX

Anonymous — Mon, 20 Jul 2020 01:18:10 GMT

Hi Anonymous

I want to add one more column in the concatenate e.g.