Forum Discussion

prakchan499's avatar
prakchan499
Advocate I
9 years ago

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 ...
BetterCallFrank's avatar
BetterCallFrank
Resolver IV
9 years ago

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

prakchan499's avatar
prakchan499
Advocate I
9 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

    • prakchan499's avatar
      prakchan499
      Advocate I
      9 years ago

      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.

       

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

      Thanks

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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