Page 1 of 2 12 LastLast
Results 1 to 10 of 12

How to show text into a pivot table

This is a discussion on How to show text into a pivot table within the Power BI forums, part of the Question Forums category; Hi there, I'm a bit new to this and hopefully you can give me an answer. I'm trying to put ...

  1. #1
    New Member
    Join Date
    Mar 2012
    Posts
    8

    Default How to show text into a pivot table


    Hi there,

    I'm a bit new to this and hopefully you can give me an answer. I'm trying to put survey results into a power pivot table and for the questions that relates to an answer it all goes perfect. But...if people have the choice to fill in text, how can I show this in a nice way in Power pivot?

    One other question, can I limit the vertical slicer. For instance there are 20 question (1 til 20) and I only want to be able to choose from 10 till 14 to show in the slice, is this possible (without excluding it in the database file)

    Thanks in advance

  2. #2
    Board Regular
    Join Date
    Jan 2012
    Location
    Ohio
    Posts
    242

    Default Re: How to show text into a pivot table

    To return a text value from your source tables as a measure, try the following measure formula pattern:

    Code:
    =IF(COUNTROWS(VALUES(Table[TextColumn])) > 1, BLANK(),
       VALUES(Table[TextColumn])
     )
    And your other question: no there is no way to limit what shows up on the slicer without modifying your underlying table data, sorry.

  3. #3
    New Member
    Join Date
    Jan 2012
    Location
    Reading, UK
    Posts
    31

    Default Re: How to show text into a pivot table

    Hi Michelv

    Rob's given you the goods on part one of your question. With regard to part 2, Rob is right, but there is a possibly "tenuous" way of doing it without having to hack your data depending on what you're trying to show. I've done something similar, but not in production!

    Create a linked table with the question numbers that you want to include, but don't relate it to any tables in your dataset. Somthing like this:

    Q
    10
    11
    12
    13
    14


    I've called this Table2 and your core table Table1.

    Use the following DAX measure


    =CALCULATE(
    VALUES(Table1[Answer]),
    FILTER(Table1,
    COUNTROWS(VALUES(Table1[Answer]))=1),
    FILTER(table1,
    COUNTROWS(FILTER(VALUES(Table1[Question]),
    EARLIER(Table1[Question])=VALUES(Table2[Q])))>0)
    )

    Make sure that you have a slicer for Table2[Q] connected to your pivot. If only one slicer option is selected, it will provide results. If more than one value is selected, it will display nothing but shouldn't give you any ugly warnings. This slicer will only have the limited set of question numbers that you want to display.

    To explain, the first filter makes sure that you only have one answer to display. This is the same as Rob's IF statement in effect. If you have multiple answers to the same question, your pivot will need to display a row reference that ensures that you'll get just one answer on that row. The second filter limits results to those questions that have values in the secondary table.

    Row LabelsAns
    12
    1First answer to question
    2Second answer


    I've got a feeling that you can get this to work with multiple question selections, but I haven't tried it.

    I hope this helps and good luck
    David

  4. #4
    New Member
    Join Date
    Jan 2012
    Location
    Reading, UK
    Posts
    31

    Default Re: How to show text into a pivot table

    Tweaked the measure a bit and it now works with multiple selections on the slicer:


    =IF(COUNTROWS(VALUES(Table1[ID]))=1,
    CALCULATE(
    VALUES(Table1[Answer]),
    FILTER(Table1,
    COUNTROWS(FILTER(Table1,
    EARLIER(Table1[Question])=VALUES(Table2[Q]))
    )>0)
    )
    )

    The only health warning is that the question number from Table2 (Table2[Q] in this case) has to feature on the pivot.

    Hope this gives you what you need.


  5. #5
    New Member
    Join Date
    Mar 2012
    Posts
    8

    Default Re: How to show text into a pivot table

    Thank you very much for your replies guys.
    I'll try to put it into practice today and hopefully it works for me.
    I appreciate it!

  6. #6
    New Member
    Join Date
    Mar 2012
    Posts
    8

    Default Re: How to show text into a pivot table

    David, I'm trying my best but for some reason I can't get it to work.
    Is it possible you can give me a working example, I thought perhaps you've created one for testing your formula.

    Thanks for your help so far!

  7. #7
    New Member
    Join Date
    Jan 2012
    Location
    Reading, UK
    Posts
    31

    Default Re: How to show text into a pivot table

    I'll message you directly and we can report back when the solution is working.

    Thanks

  8. #8
    New Member
    Join Date
    Mar 2012
    Posts
    8

    Default Re: How to show text into a pivot table

    The solution I came up with was to create Views in the database to show the answers in a slicer and to limit the questions I did the same.

    Unfortunately for some reason I can't create relations between the views and the tables. Well actually, Powerpivot let's me create them (and accepts them as well) but they don't work.

  9. #9
    New Member
    Join Date
    Mar 2012
    Posts
    8

    Default Re: How to show text into a pivot table

    BTW, I was very grateful, David was a big help to me with (all) my questions related to Powerpivot.

  10. #10
    New Member
    Join Date
    Mar 2012
    Posts
    8

    Default Re: How to show text into a pivot table

    Quote Originally Posted by michelv View Post
    Unfortunately for some reason I can't create relations between the views and the tables. Well actually, Powerpivot let's me create them (and accepts them as well) but they don't work.
    Everything works, I've copied a column from an existing table into another table with the exact same data. I'm not sure but is it possible that powerpivot won't except multiple relations with the same column/table?!

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com