4

I'm creating a PowerBI presentation that pulls data from a SharePoint list. The list has columns that correspond to questions from a form, and rows that correspond to individual items (i.e., each time someone fills out the form, it creates a new row in the table that contains their answer to each question).

Below is an example table showing what this looks like (using made up questions and data for privacy reasons):

example table

For most purposes, I want to query an individual question (i.e., a column), and be presented with a list of items. This is easy: If I want to know who didn't shower today, I just make a donut chart, fill in Column B in its Legend and Values fields, and then create a table next to it with Column A in its Values field. Now, I've got a donut chart showing me that 2/3 of respondents haven't showered today, and if I click on the "no" section of the chart, Alice disappears from the table, revealing that Bob and Carol are the gross ones:

gross people who didn't shower

However, for one element of the presentation, I want to do the reverse: Allow someone to specify an item, and look up corresponding questions. Specifically, I want to be able to check which fields were left blank on a specific item. It's not obvious to me how to do this, because field names (which are what I'm using to represent the questions) aren't something PowerBI seems to expect you to need to look up.

Is there any way to create a visualization or set of visualizations that will present a list of values from one column (e.g., a list of names in the example table above), and when a specific name is clicked on, show a list of fields that were left blank in the item with that name?

1 Answers1

1

I haven't seen such a functionality in analytical reporting engines I know about. Their purpose is to report values of data set without additional tricks like this. So you need to do the trick by yourself – convert special data into ordinary data – before your data enter final stage of reporting.

Most viable solution may exist using a helper field calculated beforehand (when, it depends on type of the engine). So if you auto-fill that field with calculated values (e.g. using a report field formula in reporting engine or triggered event right after the form is filled or – in worst case – scheduled task updating calculated values several times a day), you can report its content like a normal field. For example, helper field Unanswered for Name = Dennis (D)
will contain Question_Shower Question_Feathers.

This approach using helper fields for more complex (or less typical) requirements for values is a standard approach I have seen widely used in my area of the industry. If I would get a project to implement what you described in the question, I would use exactly this, too.

For example, the formula for the above calculated (derived) field in pseudo-code would be

Unanswered :=
  RTRIM(
    IF(ISBLANK(Answer_Shower), "Question_Shower ", "") &
    IF(ISBLANK(Answer_Lamps), "Question_Lamps ", "") &
    IF(ISBLANK(Answer_Feathers), "Question_Feathers ", "")
  )

(RTRIM() here removes possible trailing space. I used spaces as separators because of easier trimming of last one. Other separators can be used, too, with added complexity of the calculation.)

miroxlav
  • 14,845