Print the column values multiple times using DAX

[Anonymous]

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,

[Anonymous]

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

D

[Anonymous]

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

[Anonymous]

Hi @Anonymous 

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

[lbendlin]

What's wrong with using REPT() ?

[Anonymous]

@lbendlin I need a delimter also to seperate the text

[lbendlin]

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

[Anonymous]

@lbendlin

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

Best
D

[lbendlin]

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

[Anonymous]

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

Best
D

[AntrikshSharma]

This looks like a job for Power Query and not DAX

[Anonymous]

Hi @AntrikshSharma 

A power Query solution will work too.

Cheers,

[AntrikshSharma]

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

[Anonymous]

@AntrikshSharma  Thanks!!

[amitchandak]

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

[Anonymous]

Hi Amit,

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

Hopefully this explains !

Cheers,

[Anonymous]

@amitchandak 

Does the above answer help you to understand the problem?

Cheers,