Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
prakchan499
Frequent Visitor

Matrix Table - No Aggregation (Just cross table with text values)

Hi,
In the excel, we have option called no-aggregate in pivot table, where values including text just appears as is. however in Matrix table we need measure with aggregated numeric value,
Refer my below screen, where I have questions as columns, and answers as values (alpha-numeric), now how this can be achieved is my problem without aggregation.

 

Capture.PNG

 

6 REPLIES 6
BetterCallFrank
Resolver IV
Resolver IV

Hi @prakchan499

you could have a measure returning text for each column you want to display that way,

e.g.

DisplayAsText = CONCATENATEX( table, table[yourTextColumn], ", ")

does this beahve like you want it to?

 

HTH,

Frank

Hi Frank,

 

I've created this measure but I'm not sure how this should then be applied to the matrix?  here is the measure

 

measure

 

And the matrix prior to adding the measure as value:

prior

 

It appears to just give me a huge table comma separated when the measure is added as values

 

 

I can't seem to get it to separate out along the columns

Any help would be greatly appreciated!

Hey Frank,
I tried this function in my PBI (Direct Query) measure as well as in SSAS Tabular (2012/2014) DAX expression, it seems like unavailable in my environment, let me konw if I missed anything.
Thanks for your response.

Hi @prakchan499,

Do you connect to SQL Server database using "DirectQuery" mode? If so, what is the problem after you create the measure using BetterCallFrank's formula.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia, 
Since my data model is in SSAS  Tabular model, I am seeking solution in SSAS, however I tried the same in another dashboard where I am pulling data using Direct Query, there it was not supporting either.Capture.PNG

 

However in case of SSAS 2012 SP1 (1103), this function isn't available.

Thanks

 

Hi @prakchan499,

 I am able to use CONCATENATEX function after I connect to SQL Server database from Power BI Desktop via DirectQuery mode.

And as stated in this article, this CONCATENATEX  function is included in SQL Server 2016 Analysis Services (SSAS), You are not able to use it in SSAS 2012 or SSAS 2014.

For how to concatenate results of expression evaluated for each row in a table in SSAS data source, you can post the question in the SSAS forum to get better support. 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.