Forum Discussion

madawak's avatar
madawak
Frequent Visitor
8 years ago

Display VARCHAR values in a Matrix Table

Hi,

 

I have a table with two columns as below;

 

Assessment   Result

Test 1              84

Test 2              74

Test 3              A

 

 

I'm expecting to display these results in a Matrix table in Power BI as below;

 

 

Test 1     Test 2    Test 3

  84           74         A

 

I can get the numeric values to work in the Matrix table by creating a measure with SUM() and using this new measure as a value in the Matrix table. However, I'm unable to get results like 'A' to show in the table.

Any ideas please. 

 

 

Thanks.

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Try this measure:

     

    Measure 8 = MAX([Result]) & ""

    I have a feeling that this won't actually work for your full use case, but it works for the data you provided. If it doesn't work for your full use case, provide more data that more accurately describers your use case.

    • madawak's avatar
      madawak
      Frequent Visitor
      8 years ago

      Hi Greg,

       

      Thanks for your message.

      I tried to create the suggested measure and it didn't work as you expected in my use case.

       

      The scenario is as below;

       

      I have a table which shows students' subjects and their marks/grades for different assessments as below (I have a list of students in a Slicer).

       

       

      However, I'm trying to display this dataset in a Matrix table as below;

       

      I've created a tabular model in SSAS and that is where I have all my measures.

      Matrix table requires an aggreated field to display the value (Result in this instance) and I'm unable to create an aggregated result measure as both Term 1 A&E Grade and Term 1 CIE Eqv Gr have non-numeric values as the SchoolAssessmentResultText.

       

      Hope this makes sense and please let me know if you need further details.

       

      Thanks.

       

      Regards,

      Madawa

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        Create a Pivot in the Query Editor.  In the Query Editor, click on the second column's heading and click on Pivot column.  In the Aggregation drop down, select "Dont aggregate"

         

        Hope this helps.