CONCATENATEX - I want to concatenate a value for selected dates

[stanleycj]

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

[tamerj1 · Accepted solution]

@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. 

[tamerj1]

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

[stanleycj]

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

[tamerj1]

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

[stanleycj]

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]

[tamerj1]

@stanleycj 

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

[stanleycj]

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.

[stanleycj]

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. 

[tamerj1 · Accepted solution]

@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. 

[tamerj1]

@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. 

[stanleycj]

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

[tamerj1 · Accepted solution]

@stanleycj 
You should have mentioned that from the beginning.

Please refer to attached sample file with the updated solution

[tamerj1]

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

[rautaniket0077]

Please provide the data (sample) for solving.

[stanleycj]

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).