topic Re: CONCATENATEX - I want to concatenate a value for selected dates in DAX Commands and Tips

CONCATENATEX - I want to concatenate a value for selected dates

stanleycj — Fri, 10 Mar 2023 13:20:43 GMT

Dear All,

Greetings!

Below screenshot is my expected report output in matrix table. I am using date table on slicer, and i was selected two months (Dec-22 & Oct-22).

In matrix table

rows: id, name and "concat of st" measure.

column: date table [month,year]

values: st, dr, cr

the measure "concat of st" should concatenate the value of "st" for the selected month,year.

Looking for experts advise.

Regards,

Stanley R

Re: CONCATENATEX - I want to concatenate a value for selected dates

rautaniket0077 — Sun, 12 Mar 2023 13:24:11 GMT

Please provide the data (sample) for solving.

Re: CONCATENATEX - I want to concatenate a value for selected dates

tamerj1 — Sun, 12 Mar 2023 16:25:22 GMT

Hi stanleycj
You may try the following:

Create a Year Month table using the following DAX

Year Month Table = UNION ( ALLNOBLANKROW ( 'Date'[Year Month], 'Date'[Year Month Key] ), { ( "Concat of", 1 ) } )

Create relationship with the main Date table as follows

The the measure [St] can be modified as

St2 = IF ( SELECTEDVALUE ( 'Year Month Table'[Year Month Key] ) = 1, CONCATENATEX ( ALLSELECTED ( 'Year Month Table' ), [St] ), [St] )

Place [St2] measure in the matrix instead of [St] then edit the name in visual pane as needed

Re: CONCATENATEX - I want to concatenate a value for selected dates

stanleycj — Mon, 13 Mar 2023 09:35:36 GMT

Dear rautaniket0077, tamerj1 ,

Greetings!

Kindly find the sample data and output sample fyr and thank you for your response.

Table: MonthDetail_Tbl

id	name	st	val	MthYr
1	abc	1	100	Dec-22
2	xyx	2	300	Dec-22
3	sdf	2	300	Dec-22
4	wer	3	1000	Dec-22
5	jytt	1	100	Dec-22
6	brrtg	1	543	Dec-22
7	kuh	2	235	Dec-22
8	sdfs	1	7546	Dec-22
1	abc	2	100	Jan-23
2	xyx	2	50	Jan-23
3	sdf	1	300	Jan-23
4	wer	2	1000	Jan-23
5	jytt	2	100	Jan-23
6	brrtg	1	543	Jan-23
7	kuh	3	235	Jan-23
8	sdfs	2	7546	Jan-23

Filtering the MthYr by slicer on Date_Tbl

Date_tbl.MthYr and MonthDetail_Tbl.MthYr has relationship.

Table: Date_Tbl
MthYr	Quarter	QrYr	Year	Days in Month	Index
FEB-23	1	2023,Q1	2023	28	1
JAN-23	1	2023,Q1	2023	31	2
DEC-22	4	2022,Q4	2022	31	3
NOV-22	4	2022,Q4	2022	30	4
OCT-22	4	2022,Q4	2022	31	5
SEP-22	3	2022,Q3	2022	30	6

expected report output. (In matrix: I have kept id, name, concat of st in rows, MthYr in columns and st, val in values).

Re: CONCATENATEX - I want to concatenate a value for selected dates

tamerj1 — Mon, 13 Mar 2023 12:24:34 GMT

Hi stanleycj
It's not an optimum solution but seems to serve the requirement. Please refer to attached file for more details..

Re: CONCATENATEX - I want to concatenate a value for selected dates

stanleycj — Wed, 15 Mar 2023 11:24:34 GMT

Dear tamerj1 ,

Thank you so much for your kind support and regret for my late reply.

I am getting the error: Cannot convert value '' of type Text to type Number. when i am selecting "Concat of"

I tried with replacing the datatype, but i couldn't achive it.

Could you please suggest me.

Regards,

Stanley R

Re: CONCATENATEX - I want to concatenate a value for selected dates

tamerj1 — Wed, 15 Mar 2023 11:49:56 GMT

stanleycj
Would you please share a screenshot of the error and what you have achieved so far?

Re: CONCATENATEX - I want to concatenate a value for selected dates

stanleycj — Wed, 15 Mar 2023 12:32:48 GMT

Dear tamerj1 ,

Added screenshot fyr.

QRY_TBL

Qry_Apd_Tbl

I am using the Qry_Apd_Tbl in slicer

Measure i created as you suggested:

Stage Compare =

IF (

SELECTEDVALUE ( Qry_Apd_Tbl[Index] ) = 0,

VALUE (

CONCATENATEX (

ALLSELECTED ( Qry_Apd_Tbl ),

CALCULATE ( SUM ( 'DMP VW_DATA'[ST_SKEY] ) )

)

SUM ( 'DMP VW_DATA'[ST_SKEY] )

)

when we select "Movement" from the slicer, getting below error.

Release:
December 2022

Product Version:
2.112.603.0 (22.12) (x64)

Error Message:
MdxScript(Model) (165, 5) Calculation error in measure 'DMP VW_DATA'[ST_SKEY]: Cannot convert value '' of type Text to type Number.

OS Version:
Microsoft Windows NT 10.0.19045.0 (x64 en-US)

CLR Version:
4.8 or later [Release Number = 528372]

Re: CONCATENATEX - I want to concatenate a value for selected dates

tamerj1 — Wed, 15 Mar 2023 16:07:36 GMT

stanleycj

What is the data type of the 'DMP VW_DATA'[ST_SKEY] column? Can you provide a screenshot?

Re: CONCATENATEX - I want to concatenate a value for selected dates

stanleycj — Thu, 16 Mar 2023 06:46:56 GMT

Dear tamerj1

I tried with Union query as you suggested and then to avoid this datatype error, I created a static table and merged together and tried. but still the error persist.

Kindly find the attached screenshot fyr.

Since, its a sensitive data couldn't share full screen shot and thanks for understanding.

Also, I feel the datatype issue on Qry_Apd_Tbl, Coz when we select the value "Movement" on slicer we are getting the error, whereas SUM ( 'DMP VW_DATA'[ST_SKEY] ) is working fine while selecting the month(s).

Stage Compare =

IF (

SELECTEDVALUE ( Qry_Apd_Tbl[Index] ) = 0,

Thank you.

Re: CONCATENATEX - I want to concatenate a value for selected dates

stanleycj — Thu, 16 Mar 2023 08:05:43 GMT

Dear tamerj1 ,

Just now, I tried without value() and there is no error. whereas i am getting all the other fields on the movement group.

Re: CONCATENATEX - I want to concatenate a value for selected dates

tamerj1 — Thu, 16 Mar 2023 09:05:53 GMT

stanleycj

great!
yes, there is no way to hide them automatically rather you need to follow my sample file and hide them manually after disabling text wrapping.

Re: CONCATENATEX - I want to concatenate a value for selected dates

tamerj1 — Thu, 16 Mar 2023 09:05:59 GMT

stanleycj

great!
yes, there is no way to hide them automatically rather you need to follow my sample file and hide them manually after disabling text wrapping.

Re: CONCATENATEX - I want to concatenate a value for selected dates

stanleycj — Mon, 20 Mar 2023 12:52:14 GMT

Dear tamerj1 ,

Greetings!

Today, I was requested to do filter on concatenated value. Hence I have created one more measure as mentioned below

Stage Status Desc =

IF([Stage Compare]=22, "No Change from Stage 2",

IF([Stage Compare]=23, "Stage 2 to 3",

IF([Stage Compare]=32, "Stage 3 to 2"

)))

and then, I have created a table as mentioned below.

Stage Status =

var st = SUMMARIZE('DMP VW_DATA','DMP VW_DATA'[ACCNUMBER], "Status",[Stage Status Desc])

RETURN

SUMMARIZE(st,[Status])

and, I have added "status" field in the slicer. whereas its shows only "blank".

Could you please suggest me, what mistake i have made.

Thanks a lot.

Stanley R

Re: CONCATENATEX - I want to concatenate a value for selected dates

tamerj1 — Mon, 20 Mar 2023 14:58:18 GMT

stanleycj
You should have mentioned that from the beginning.

Please refer to attached sample file with the updated solution